Split identical column values by their unique properties

[ca_acm]

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).

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!

[JasperDJ]

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:

---- 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:

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

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

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"

[ca_acm]

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!