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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ca_acm
New Member

Split identical column values by their unique properties

Hello,

 

I have a table with a column which has duplicated ids, which then differs in the second and third column by different properties (example in the screenshot).

 

ca_acm_1-1692784440934.png

 


How can I create a table that has in the first column the unique ids and the others properties (ex. A,B,C,D) lined up in the each row?
I tried to calculate them as an individual filter, but when I create some graphs, the slicer don't work between each properties.

 

Thanks!

2 REPLIES 2
JasperDJ
Helper I
Helper I

Hi @ca_acm ,

 

I hope I'm understanding your question correctly.

Maybe the easiest solution is to have three tables, one including the unique Id numbers (Column 1)  with their properties, one table with unique Atribute Id's (Column 2), and the third would be the atribute_ids per Id number (the table from your screenshot).

Example:

JasperDJ_0-1692788014980.png

 

 

---- EDIT -----

If you want to show the Atributes in the same row / different columns in the table you could try this:

Create however many columns you need for the atributes (A through D for example) and fill the column with the values from the Atribute_id column when it matches:

JasperDJ_0-1692788540877.png

 

Then use a Group By to find the max value in the newly made columns:

 

JasperDJ_1-1692788622011.png

 

And your output should look like this, essentially flipping the table horizontally:

JasperDJ_2-1692788665747.png

 

Here is a sample of the code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcq5DQAwCATBXohJfNCAny4Q/bdhyZY2m2CqbJjbtPavhTY6T+KJJ554wQte8IKXvOQlL//rCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Id number" = _t, Atribute_id = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id number", Int64.Type}, {"Atribute_id", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Atribute_id A", each if [Atribute_id]="A" then [Atribute_id] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Atribute_id B", each if [Atribute_id]="B" then [Atribute_id] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Atribute_id C", each if [Atribute_id]="C" then [Atribute_id] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Atribute_id D", each if [Atribute_id]="D" then [Atribute_id] else null),
#"Grouped Rows" = Table.Group(#"Added Custom3", {"Id number"}, {{"Atribute_A", each List.Max([Atribute_id A]), type nullable text}, {"Atribute_B", each List.Max([Atribute_id B]), type nullable text}, {"Atribute_C", each List.Max([Atribute_id C]), type nullable text}, {"Atribute_D", each List.Max([Atribute_id D]), type nullable text}})
in
#"Grouped Rows"

Dear @JasperDJ ,

I'll keep the solution in mind for next time I have similar problem.

I didn't manage to make it work with M syntax, but I make it work by reassigning relationship with the three tables alone (there were other column properties in common that I had used as bridge).

 

Thanks for the tip, i'll try to do it on a new dataset!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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