Forum Discussion
Formatting and Grouping rows
I have a chart that looks like this
I would like the rows to be grouped so their are subcategories. For example, this chart
For this example, I would like all the rows with the key word 'razor' to be under one category, and 'other' in another category
It would result in something like this
RAZORS 26 27
Disposable razors 4 5
razor 12 14
razors 10 8
OTHER 3 3
shampoo 2 1
shave 1 2
Help is greatly appreciated
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsksLsgvTkzKSVUoSqzKLypW0lEyAWJTpVidaCWwEJBnaAQiTBQQgiB1hgZAwgIsVpyRmFuQnw/kg1XCxMpSwTyQaGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains( Text.Upper([Column1]),"RAZOR") then "RAZORS" else "OTHER") in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
1 Reply
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsksLsgvTkzKSVUoSqzKLypW0lEyAWJTpVidaCWwEJBnaAQiTBQQgiB1hgZAwgIsVpyRmFuQnw/kg1XCxMpSwTyQaGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains( Text.Upper([Column1]),"RAZOR") then "RAZORS" else "OTHER") in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".