Power Query - Fill Down with special text conditions

1
votes

I am trying to clean a large detailed profit and loss report and it includes Category & Sub Category columns. I have successfully filled down the Category column. However, the Sub Category column only has subcategory names scattered throughout the report therefore a normal fill down won't work. How do I Fill Down starting where there exists a Sub Category value but only continues down to the Sub Category Total description?

Example in the picture below: Sub Category = "Closing stock - cattle"

Fill Down with those exact words - Closing stock - cattle until the cell that reads Total Closing stock - cattle. Then Fill Down from the new Sub Category in the same format.

Basically, the word Total is a very important part as I do not want that particular value Filled Down. Please note, there can be hundreds of rows that do not have a Sub category.

enter image description here

enter image description here

2
powerquerym
Looking at above image, the Total rows always have something right below them. If so, just filling-down the Sub-Category column seems to do exactly what you want. Whats the issue? – horseyride
Hi Horseyride, I've attached another picture from further down the table. The majority of the column does not have a Sub category listed. Eg. Out of 100 different Categories, only 5 have Sub Categories. The 2nd picture shows that if I Filled Down then the "Total CATTLE-Other" value will be repeated (and you can see that is unrelated to the Category of CHICK PEA) – sldonovan77

2 Answers

1
votes

Try this

#"Duplicated Column" = Table.DuplicateColumn(#"PreviousStep", "Subcategory", "Dupe"),
#"Filled Down" = Table.FillDown(#"Duplicated Column",{"Dupe"}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "Custom", each if [Subcategory]=null then (if Text.Contains([Dupe], "Total") then [Subcategory] else [Dupe]) else [Dupe]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Subcategory", "Dupe"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Subcategory"}})
1
votes

Filter out the total rows into their own table, fill down, then append the total rows back in.

let
    StartTable = <Your Data Source>,
    NoTotals = Table.SelectRows(StartTable, each not Text.Contains([Sub Category], "Total") or [Sub Category] = null),
    OnlyTotals = Table.SelectRows(StartTable, each Text.Contains([Sub Category], "Total")),
    FillDown = Table.FillDown(NoTotals, {"Sub Category"}),
    Append = Table.Combine({FillDown, OnlyTotals})
in
    Append

If you need it to get back to the starting order, add an index column before doing any filtering and then sort by that index as your last step.