Categorize rows based on value in other columns

[Plurp]

Hello!

I've got a set of data that formatted similar to the table on the left in the picture below. I want to be able to categorize the rows based on the values the values in column1, resulting in a table similar to that on the right. For example, for the first category, I would normally find the row index for "Category 27: Toys", the row index for "Total for Category 27", and set the category for the rows between those indices as "Toys". However I'm having trouble doing this in power query, and can't seem to find the right way to do it. Is there a straightforward formula/way of extracting the row indices of those specific strings in power query, then setting the value of the Category column to a specific value between the two indices? I don't mind setting this up manually for each category as my actual data only has 5 categories. 

Appreciate any help! Thank you! 

[Daryl-Lynch-Bzy · Accepted solution]

Hi @Plurp , you can consider using Text.Contains search and Text.AfterDelimiter exact the Category, and the fill down to achieve the result.  Then remove the rows containing Category and Date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL6pUMDK3UgjJryxW0lFSitWJVnIBSsDYhob6hvpGBkbGQAEVcz0DA5ioEVzUEEnUGC5qDBMNyS9JzFFIyy9SQLIQrA+uES5hamCl4Jafn4LNJRZIhpvBdFromyDcYYQQNYWLWiIEzTA9gsVxpgZg55uAlcQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = false]), _n = (( Currency.Type ) meta [Serialized.Text = false]) in type table [Column1 = _t, Cost = _n]),
    #"Added Custom" = Table.AddColumn(Source, "Category", each if Text.Contains( [Column1] , ": ") then Text.AfterDelimiter( [Column1] , ": ") else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Column1], "Category") and not Text.Contains([Column1], "Date")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Date"}}),
    #"Parsed Date" = Table.TransformColumns(#"Renamed Columns",{{"Date", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Parsed Date", {{"Cost", each Text.AfterDelimiter(Text.From(_, "en-GB"), "$"), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Cost", Currency.Type}})
in
    #"Changed Type"

[Plurp]

Perfect! This solution is far more robust than even what I had in mind. Greatly appreciate your help with this.