Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello, everyone 🙂
My table follows this structure:
ID | Level | Code |
111 | D | 53.1 |
111 | K | 75.1 |
222 | H | 82.1 |
333 | G | 75.1 |
333 | G | 82.1 |
I would like to create a new table, and in that table, each ID has to appear only once and its Level values to become columns and Code values to be associated with these columns. Based on the example table above, the expected result would be this:
ID | D | K | G | Y | H |
111 | 53.1 | 75.1 | |||
222 | 82.1 | ||||
333 | 75.1/82.1 |
It is possible to do something like that?
Solved! Go to Solution.
@Anonymous
In Power Query, Select LEVEL Column, go to Transform Tab and Click on Pivot Column.
In the Value Column, choose CODE, It will pivot as you expected
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous , You can pivot
https://radacad.com/pivot-and-unpivot-with-power-bi
OR You can use matrix visual, put id on row and level on column and code on value
Always more than one way to skin a cat!
Proud to be a Super User!
Hey @Anonymous
I think I might have a workable solution
I've use the same data as you for testing
You should be able to perform this one the Power Query side by using a combination of grouping, a little M manipulation, and a pivot.
I've gone ahead and copied the steps I've taken below from the advanced editor and the output I had gotten
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRcgFiU2M9Q6VYHZiQNxCbm0KFjIyMgFwPILYwggoZGxsDue7IqhBCEFWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Level = _t, Code = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Level", type text}, {"Code", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Unique ID", each [ID]&"_"&[Level]),
#"Grouped Rows" = Table.Group(#"Added Custom", {"ID", "Level"}, {{"Values", each Text.Combine([Code], "/"), type nullable text}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Level]), "Level", "Values")
in
#"Pivoted Column"
First start by performing a group by but you will need to change the M line that is automatically entered from List.Sum over to what I've included in my steps as "Text.Combine". This will account for the multiple values in an individual column.
The only thing with this solution is that those two values are being treated as text. If you want them to be numerical you may require a dax-forward solution.
@Anonymous
In Power Query, Select LEVEL Column, go to Transform Tab and Click on Pivot Column.
In the Value Column, choose CODE, It will pivot as you expected
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |