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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors