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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RokuCap
Helper I
Helper I

Using Cell substring as Column header to Group Row Data

I have a table with multiple columns for each unique row/key. Each column cell (e.g. "A: ID01") has two parts: a category (A, B, C, D) and an ID (ID01 - ID05). I'm trying to bring the unique categories to the column headers and move the IDs under the corresponding category column, if that makes sense. They will still be in the same row, just grouped under the correct category column.

 

Apologies for the unclear title as I wasn't sure how to describe the issue. Any tips would be appreciated!

 

Convert from:

 Column1Column2Column3Column4
Row1A: ID01B: ID01C: ID03 
Row2B: ID02A: ID02E: ID02 
Row3C: ID01B: ID03A: ID04 
Row4E: ID01C: ID02B: ID05A: ID03

 

To:

 ABCE
Row1ID01ID01ID03 
Row2ID02ID02 ID02
Row3ID04ID03ID01 
Row4ID03ID05ID02ID01

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRS8HQxMFTSUXKCs5zBLGMgSylWJxoqYQTkOsJZrnAWWIkzminGcMUmMCWuaMYbwRWbwhUbK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / Table.ColumnCount(Source),0), type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Divided Column", "Value", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Value.1]), "Value.1", "Value.2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

 👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRS8HQxMFTSUXKCs5zBLGMgSylWJxoqYQTkOsJZrnAWWIkzminGcMUmMCWuaMYbwRWbwhUbK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / Table.ColumnCount(Source),0), type number}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Divided Column", "Value", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Value.1]), "Value.1", "Value.2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

 👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

Thanks Vijay! This function worked great. Only question I have is why you added indexes and then divided the columns. i.e. the steps below:

  #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / Table.ColumnCount(Source),0), type number}}),

As the "Index" column isn't referenced in any of the subsequent steps before being removed in the last step.

When you insert an index with base 1, it will be 1,2,3,4.....

Number.RoundUp([index]/3,0) will generate a series like

1,1,1,2,2,2,3,3,3

If you replace 3 with 5, then series would be

1,1,1,1,1,2,2,2,2,2,3,3,3,3,3

This is to a provide a column over which pivoting can be done.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors