none
Converting Rows to Unique Columns RRS feed

  • Question

  • Hello,

    I am using Power Query to get table data from the web. It comes in the following format:

    Column1   Column2

    Name1      NamedGroup1

    Name1      NamedGroup2

    Name2     NamedGroup3

    Name3     NamedGroup1

    Name3     NamedGroup4

    ... 100 names

    I would like to convert the data to look like this:

    Column1      Column2                Column3              Column4

    Name1         NamedGroup1       NamedGroup2    NamedGroup3

    Name2        NamedGroup3

    Name3        NamedGroup1        NamedGroup4

    I have tried Pivots, Indexing, Transposing. None of these provide the output that I'm after. Any help would be very much appreciated. 

    Saturday, November 5, 2016 8:26 PM

Answers

  • This is one possibility:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0gHTKe5F+aUFhkqxOtjEjeDiRijixnBxYxzmoIqbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
        #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Column0"}}),
        Groups = Table.Group(#"Renamed Columns", {"Column0"}, {{"All", each Table.FromRows({_[Column2]}), type table}}),
        Expand = Table.ExpandTableColumn(Groups, "All", List.Union(List.Transform(Groups[All], each Table.ColumnNames(_))), List.Union(List.Transform(Groups[All], each Table.ColumnNames(_))))
    in
        Expand

    Just replace the Source-expression by a reference to your table.


    Imke Feldmann TheBIccountant.com

    Sunday, November 6, 2016 2:32 PM
    Moderator
  • Hi Colin, thx for reminding me! It felt a bit clumsy, but I couldn't remember the other one :-)

    Do you have an idea how one could improve the function-version of it?:

    (Table as table, ColumnName as text) =>
    let
        fnExpandAllColumns = Table.ExpandTableColumn(Table, ColumnName, Table.ColumnNames(Table.Combine(Table.ToColumns(Table.SelectColumns(Table, ColumnName)){0})))
    in
        fnExpandAllColumns
    To select the column in list-format is a bit clumsy here - do you have a more elegant solution for it?

    Imke Feldmann TheBIccountant.com

    Sunday, November 6, 2016 4:00 PM
    Moderator

All replies

  • This is one possibility:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0gHTKe5F+aUFhkqxOtjEjeDiRijixnBxYxzmoIqbKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
        #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Column0"}}),
        Groups = Table.Group(#"Renamed Columns", {"Column0"}, {{"All", each Table.FromRows({_[Column2]}), type table}}),
        Expand = Table.ExpandTableColumn(Groups, "All", List.Union(List.Transform(Groups[All], each Table.ColumnNames(_))), List.Union(List.Transform(Groups[All], each Table.ColumnNames(_))))
    in
        Expand

    Just replace the Source-expression by a reference to your table.


    Imke Feldmann TheBIccountant.com

    Sunday, November 6, 2016 2:32 PM
    Moderator
  • Hi Imke, you outdid my solution by a step. :) However, I'm wondering why you chose such a complex Expand formula, instead of the one you used in a recent solution! i.e.

    Table.ExpandTableColumn(Groups, "All", Table.ColumnNames(Table.Combine(Groups[All])))

    The optional parameter isn't required in "Table.ExpandTableColumn" since we're not changing the column names.

    Sunday, November 6, 2016 3:10 PM
  • Hi Colin, thx for reminding me! It felt a bit clumsy, but I couldn't remember the other one :-)

    Do you have an idea how one could improve the function-version of it?:

    (Table as table, ColumnName as text) =>
    let
        fnExpandAllColumns = Table.ExpandTableColumn(Table, ColumnName, Table.ColumnNames(Table.Combine(Table.ToColumns(Table.SelectColumns(Table, ColumnName)){0})))
    in
        fnExpandAllColumns
    To select the column in list-format is a bit clumsy here - do you have a more elegant solution for it?

    Imke Feldmann TheBIccountant.com

    Sunday, November 6, 2016 4:00 PM
    Moderator
  • My equivalent function would probably look something like:

    (table as table, expandColumnName as text) as table =>
    Table.ExpandTableColumn(
        table,
        expandColumnName,
        Table.ColumnNames(
            Table.Combine(
                Table.Column(
                    table,
                    expandColumnName
                )
            )
        )
    )

    Sunday, November 6, 2016 4:54 PM
  • Thx! (... memory...)

    Imke Feldmann TheBIccountant.com

    Sunday, November 6, 2016 6:19 PM
    Moderator