locked
Combining Rows to Columns with Power Query RRS feed

  • Question

  • Hi

    I currently have a set of data that looks like this:

    Session          Group                  Name

    Session1        Group1                Name1
    Session1        Group1                Name2
    Session1        Group1                Name3
    Session1        Group1                Name4
    Session1        Group2                Name1
    Session1        Group2                Name2
    Session1        Group2                Name3
    Session1        Group2                Name4
    Session2        Group3                Name1
    Session2        Group3                Name2
    Session2        Group3                Name3

    I need to use the Names in a word mail merge, so ideally need the data in the following format, with each Name as a column, e.g.:

    Session1         Group1              Name1       Name2      Name3       Name4     
    Session1         Group2              Name1       Name2      Name3       Name4
    Session2         Group3              Name1       Name2      Name3

    But am not sure how to go about this (if it is possible). Each row may have a different number of columns (depending on the number of names in each group) but this shouldn't be an issue. Any help would be much appreciated.

    Friday, March 1, 2019 8:31 AM

Answers

  • You could group on Session and Group, create a nested index (for the headers) and then pivot the tables.

    But another solution is this:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tLs7MzzNU0lFyL8ovLQAx/BJzUw2VYnVwyxrhlTXGK2uCVdYIr71GeO01wmuvETZ7jWCyxgh7YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Session = _t, Group = _t, Name = _t]),
        #"Grouped Rows" = Table.Group(Source, {"Session", "Group"}, {{"All", each Table.FromRows({_[Name]}), type table}}),
        #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", Table.ColumnNames(Table.Combine(#"Grouped Rows"[All])))
    in
        #"Expanded All"

    Simply replace the code in the "Source"-step by a reference to your table.

    If you want to know how this works, please check out my latest blogpost: https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/ ,which explains the core mechanism of this solution.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Friday, March 1, 2019 9:12 AM

All replies

  • You could group on Session and Group, create a nested index (for the headers) and then pivot the tables.

    But another solution is this:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tLs7MzzNU0lFyL8ovLQAx/BJzUw2VYnVwyxrhlTXGK2uCVdYIr71GeO01wmuvETZ7jWCyxgh7YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Session = _t, Group = _t, Name = _t]),
        #"Grouped Rows" = Table.Group(Source, {"Session", "Group"}, {{"All", each Table.FromRows({_[Name]}), type table}}),
        #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", Table.ColumnNames(Table.Combine(#"Grouped Rows"[All])))
    in
        #"Expanded All"

    Simply replace the code in the "Source"-step by a reference to your table.

    If you want to know how this works, please check out my latest blogpost: https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/ ,which explains the core mechanism of this solution.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Friday, March 1, 2019 9:12 AM
  • Perfect, thanks very much
    Friday, March 1, 2019 11:20 AM
  • let
        源 = Excel.CurrentWorkbook(){[Name="表4"]}[Content],
        分组的行 = Table.Group(源, {"Session", "Group"}, {{"NAME", each Text.Combine([Name],","), type text}}),
        按分隔符拆分列 = Table.SplitColumn(分组的行, "NAME", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"NAME.1", "NAME.2", "NAME.3", "NAME.4"})
    in
        按分隔符拆分列
    Tuesday, March 19, 2019 7:19 AM