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

Get 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

Reply
Anonymous
Not applicable

Turn rows into columns with Distinct

Hello, everyone 🙂
My table follows this structure:

ID     Level  Code  
111D53.1
111K75.1
222H82.1
333G75.1
333G82.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    KGYH
11153.1  75.1     
222    82.1  
333  75.1/82.1    


It is possible to do something like that?

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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

Fowmy_0-1597337455296.png


________________________

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 🙂

YouTube, LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Always more than one way to skin a cat!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Baskar
Resident Rockstar
Resident Rockstar
westwrightj
Resolver III
Resolver III

Hey @Anonymous 

 

   I think I might have a workable solution

 

I've use the same data as you for testing

 

westwrightj_0-1597337242082.png

 

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"

 

westwrightj_1-1597337845743.png

 

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.

 

 

 

Fowmy
Super User
Super User

@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

Fowmy_0-1597337455296.png


________________________

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 🙂

YouTube, LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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