Forum Discussion
Pivoting a column and merging linked fields
Hi Merleau ,
you can pivot on the Type-column and use a Text.Combine-function as the aggregation function on the "Programs"-column.
Create a new query and paste this code into the advanced editor:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRcvQFEq556TmZxRlgNlBYNy8xN1UpVgemJgAkHlyQmAdUE4BDDURvSWJOZmIeLnPA4m5FqXnJWK0ywqrECF0J2AXO+UWpiXkBOJRg8RSKGmOYMY5FiUmZyVBjjLEqgfoJmxqIn3QDckqLgSz3otTUbAQX4blYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Programs = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Type", type text}, {"Programs", type text}, {"Name", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Programs", each Text.Combine(_, ", ")) in #"Pivoted Column"Enclosing a file a well.
I'm not sure I understand this request correctly.
I guess this modification of this expression:
Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Programs])
to
Table.AddColumn(#"Grouped Rows", "Custom", each List.Sort([Count][Programs]))
it looks very similar to a recently treated problem
let
Source = Table.FromRecords (Json.Document (Binary.Decompress (Binary.FromText("i65W8nRRsgIShko6SiGVBalAjqMvkB1QlJ9elJhbDOS75qXnZBZngIX9EnNTIep180DMWh24CUa4TXArSs1LRjPACMMAY4QBAWgGOBYlJmUmB6AYYIxhgCFuA4ILEvOAfggg3g/oJjjnF6Um5gUQ8AOeYPQsSczJTMyjIBixRgRJ4Qh1AwkBSUxUwjwRCwA=",BinaryEncoding.Base64),Compression.Deflate))),
group = Table.Group(Source, "ID", {"Type-Programs", each Table.PromoteHeaders(Table.Transpose(Table.Group(_, "Type", {"_", each Text.Combine(_[Programs], ", ")})))}),
tc=Table.Combine(group[#"Type-Programs"])
in
tc
I solved it using the code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lEKqSxIBVIBRfnpRYm5xUCmX2JuqlKsDkjeEMh19AUSrnnpOZnFGWA2UFg3D6HGCKbGrSg1LxmmxAhZiTHIApC4Y1FiUmZyAESJMbISQ5iS4ILEPKBNAThsAos75xelJuYFYLEJ7mDPksSczMQ8vA5G9RR2F0PNweVkTI/DrIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID", "Type"}, {{"Count", each _, type table [ID=text, Type=text, Programs=text, Name=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Programs]),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom1" = Table.AddColumn(#"Extracted Values", "Custom.1", each [Count][Name]{0}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Custom")
in
#"Pivoted Column"
But ImkeF's is so much cleaner.
I will accept it as a solution.
Thank you so much for all your contribution.
Sincere thanks to all for the solution.
How do I sort the resulting list pls? Just normal ascending order.
I would like the final list to be ordered either in M or in DAX.
That is the last requirement for this task. Sorry that I mention it this late.
Again many thanks.
I'm not sure I understand this request correctly.
I guess this modification of this expression:
Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Programs])
to
Table.AddColumn(#"Grouped Rows", "Custom", each List.Sort([Count][Programs]))
Thank you so much for all your solution and contribution.
I will accept Anonymous's last suggestion as a response because it works.
However, I am still looking for a solution in DAX.
Thank you