Forum Discussion
RichardJ
4 years agoResponsive Resident
Grouping by Date then Converting Rows for that date to Column values - Power Query question
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...
- 4 years ago
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
4 years agoSuper User
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.
- RichardJ4 years agoResponsive Resident
Ashish_Mathur - your response is awesome and has really made my day.
Thanks for the lesson on Partitions!- Ashish_Mathur4 years agoSuper User
You are welcome.