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.
Hi,
File : https://www.dropbox.com/s/ypnj7tj5dxpqh6q/Power_BI_Rows_To_Columns.pbix?dl=0
I have a table containing this data in Power Query
which is split into rows in Power Query as shown
which is then aggregated using the matrix visual
My question is to ask if anyone could advise how I can further manipulate this table in Power Query to group by/aggregate to have the same output as the matrix visual but within Power Query
This would mean keeping the date column, but creating row A as a column header, B as a column header, C,D,E,F etc
I could use the conditional column rules to add a new Column A to enter a '1' when it finds an 'A' against the Responses for that date, then apply a rule for B,C,D,.... then group by date but this seems inefficient as i'd need to revisit the rules when someone adds a new letter that doesnt have existing conditional rules as a response.
Can anyone help with the best way to approach this?
Thanks,
Richard
Solved! Go to Solution.
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNzDUNzIwMlLSUXLUcdJx1nFRitUBShijS0CETVCEXSGCpiiCQCN0XHXcIFJmaFJAHbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Responses = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Responses", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Responses", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Responses"),
Partition = Table.Group(#"Split Column by Delimiter", {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Responses", "Index"}, {"Responses", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Responses]), "Responses", "Index", List.Count)
in
#"Pivoted Column"
Hope this helps.
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNzDUNzIwMlLSUXLUcdJx1nFRitUBShijS0CETVCEXSGCpiiCQCN0XHXcIFJmaFJAHbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Responses = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Responses", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Responses", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Responses"),
Partition = Table.Group(#"Split Column by Delimiter", {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Responses", "Index"}, {"Responses", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Partition", List.Distinct(#"Expanded Partition"[Responses]), "Responses", "Index", List.Count)
in
#"Pivoted Column"
Hope this helps.
@Ashish_Mathur - your response is awesome and has really made my day.
Thanks for the lesson on Partitions!
You are welcome.
@RichardJ , Create two tables at the unpivot stage, filter one on A and another on <> A, then pivot the table<> A. Pivot column other than date
Then merge these two table into one using date
Thanks for the response @amitchandak but I have misunderstood the instructions.
Would you mind looking at the file which contains the two tables I think you'd suggested plus the merge and advise where i've gone wrong please?
https://www.dropbox.com/s/wjwu42c09f7znff/Power_BI_Rows_To_Columns%20v1.pbix?dl=0
Thanks
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |