Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi @Awollet33 ,
Using below M codes to realize it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc27DcAgEAPQXa6muA+QYdAV+e+/QSQ3uCHdk2XZY8guRUxNsgw5YIdPOOALrvANN/iBO/zCG/y/WanTFp1Ov0a/Tr9Bv6tNo02nTkzbzENVMj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, Code = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", type text}, {"Code", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"Group1", each _, type table [Account Number=nullable text, Code=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Group1],"Code")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Group1"})
in
#"Removed Columns"
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Awollet33 ,
Using below M codes to realize it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc27DcAgEAPQXa6muA+QYdAV+e+/QSQ3uCHdk2XZY8guRUxNsgw5YIdPOOALrvANN/iBO/zCG/y/WanTFp1Ov0a/Tr9Bv6tNo02nTkzbzENVMj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, Code = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", type text}, {"Code", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Account Number"}, {{"Group1", each _, type table [Account Number=nullable text, Code=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Group1],"Code")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Group1"})
in
#"Removed Columns"
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hey @Awollet33 ,
you can do that with an Unpivot in Power Query:
Pivot and Unpivot with Power BI - RADACAD
Transpose, pivot or unpivot in Power Query? - YouTube
Hi @selimovd - Unpivot hasn't been giving me the results I am looking for me. Thank you though. Probably user error 🙃
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |