Power Query - Fill Down with conditions

[weslake77]

Hi all,
I have a profit and loss report that includes a Category column and a Sub Category column.
I filled down the Category column with great success. However, there only exists a handful of Sub Categories in a data set with hundreds of rows.
See pics below. The Sub Categories normally have a cell with the text that describes the Sub Category and then a couple of blanks rows, then the same Sub Category written with the addition of the word "Total". (See pics)
How do I Fill Down between these 2 values as a typical Fill Down will also repeat the "Total...." Sub Cat as well which isn't correct.
Any ideas??

 

[sandy666]

post a link to the shared (dropbox, onedrive or any similar) excel file with you problem

 

[weslake77]

Excel file is here:

Fill Down Issue.xls

1drv.ms

 

[weslake77]

post a link to the shared (dropbox, onedrive or any similar) excel file with you problem

Hi Sandy, file now attached

 

[sandy666]

your query table doesn't exist

 

[sandy666]

what is expected result in the sub category column ?
use xl2bb or picture to show

 

[sandy666]

I don't know what you want in the yellow section

result
Closing stock - cattle
Closing stock - cattle
Total Closing stock - cattle
Killed for rations - cattle
Killed for rations - cattle
Total Killed for rations - cattle
Opening stock - cattle
Opening stock - cattle
Total Opening stock - cattle
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
Total CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other

 

[sandy666]

or like this

result
null
Closing stock - cattle
Closing stock - cattle
Total Closing stock - cattle
Killed for rations - cattle
Killed for rations - cattle
Total Killed for rations - cattle
Opening stock - cattle
Opening stock - cattle
Total Opening stock - cattle
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
Total CATTLE - Other
null
null
null
null
null
null
null
null
null

 

[weslake77]

or like this

result
null
Closing stock - cattle
Closing stock - cattle
Total Closing stock - cattle
Killed for rations - cattle
Killed for rations - cattle
Total Killed for rations - cattle
Opening stock - cattle
Opening stock - cattle
Total Opening stock - cattle
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
CATTLE - Other
Total CATTLE - Other
null
null
null
null
null
null
null
null
null

Yes that one is what I'm after. I didn't want to attach actual power query as it has a lot of sensitive info.

 

[sandy666]

try adapt this
note: it's tailored to your example only

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF1 = Table.AddColumn(Source, "Custom", each if Text.Contains([Sub Category], "Total") then [Sub Category] else null),
    IF2 = Table.AddColumn(IF1, "Custom.1", each if not Text.Contains([Sub Category], "Total") then [Sub Category] else null),
    ReplaceErr = Table.ReplaceErrorValues(IF2, {{"Custom", null}, {"Custom.1", null}}),
    FillD = Table.FillDown(ReplaceErr,{"Custom.1"}),
    ReplaceNull = Table.ReplaceValue(FillD,null," ",Replacer.ReplaceValue,{"Custom", "Custom.1"}),
    IF3 = Table.AddColumn(ReplaceNull, "Custom.2", each if Text.Contains([Custom], "Total") then [Custom] else if [Index] > 87 then " " else [Custom.1]),
    ReplaceSpace = Table.ReplaceValue(IF3," ",null,Replacer.ReplaceValue,{"Custom.2"}),
    TSC = Table.SelectColumns(ReplaceSpace,{"Index", "Income or Expense", "Category", "Custom.2", "Date", "Type", "Num", "Description", "Amount"}),
    Ren = Table.RenameColumns(TSC,{{"Custom.2", "Sub Category"}}),
    Type = Table.TransformColumnTypes(Ren,{{"Index", Int64.Type}, {"Date", type date}, {"Amount", type number}})
in
    Type