Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Plurp
New Member

Categorize rows based on value in other columns

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! 

 

example.PNG

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"

 

DarylLynchBzy_0-1699396227367.png

DarylLynchBzy_1-1699396242461.png

 

 

View solution in original post

2 REPLIES 2
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"

 

DarylLynchBzy_0-1699396227367.png

DarylLynchBzy_1-1699396242461.png

 

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors