Grouping by Date then Converting Rows for that date to Column values - Power Query question

[RichardJ]

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

1 Source Data

which is split into rows in Power Query as shown

2 Split into Rows

which is then aggregated using the matrix visual

3 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

Would like this table to look like the matrix visual version

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

[Ashish_Mathur · Accepted 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.

[RichardJ]

@Ashish_Mathur - your response is awesome and has really made my day.
Thanks for the lesson on Partitions!

[Ashish_Mathur]

You are welcome.

[amitchandak]

@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

[RichardJ]

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

Merge results (incorrect)

Thanks