Forum Discussion

RichardJ's avatar
RichardJ
Responsive Resident
4 years ago
Solved

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

 

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

Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    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.

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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's avatar
    amitchandak
    Super User
    4 years ago

    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