Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a table with multiple columns (more than 30) in no particular order. I need to create multiple columns by pivoting one of these columns. And populate them with associated information coming from another column.
The original table is:
ID | Type | Programs | Name |
ID1 | AM | EnglishAM | ID1-name |
ID1 | PM | SpanishPM | ID1-name |
ID1 | AM | ItalianAM | ID1-name |
ID1 | AM | FrenchAM | ID1-name |
ID2 | AM | FrenchAM | ID2-name |
ID2 | PM | CoreanPM | ID2-name |
ID2 | AM | EnglishAM | ID2-name |
ID3 | PM | ArabicPM | ID3-name |
ID3 | PM | ItalianPM | ID3-name |
ID1 | AM-Plus | GreekAM-Plus | ID1-name |
After creating the columns, I would like to obtain the table below.
ID | AM | PM | AM-Plus | Name |
ID1 | EnglishAM, ItalianAM, FrenchAM | SpanishPM | GreekAM-Plus | ID1-name |
ID2 | FrenchAM, EnglishAM | CoreanPM |
| ID2-name |
ID3 |
| ArabicPM, ItalianPM |
| ID3-name |
Can somebody pls help?
Thank you
Solved! Go to Solution.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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]))
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Hi @Merleau ,
you have to sort it in the report.
Due to Power BIs underlying VertiPaq engine, you cannot enforce a sort order in the data model.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |