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.
amitchandak
4 years agoSuper User
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
- RichardJ4 years agoResponsive Resident
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=0Merge results (incorrect)
Thanks