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     which is split into rows in Power Query as...
  • Ashish_Mathur's avatar
    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.