Search code examples
powerquerym

Power Query - Fill Down with special text conditions

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

Solution

  • 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"}})