none
Power Query: Expand multiple lists within a row at once RRS feed

  • Question

  • I have a json file that I've been fairly successful importing into excel using power query. My issue now is dealing with lists in multiple columns.  I need to figure out how to expand the values of the lists at once so I only add rows once, versus multiple times.

    Example:

    Row 1 - Text | List1 {1,1,2,4} | List2 {A,B,B,D} | List3 {W,X,Z,Z}|
    Row 2 - Text | List1 {1,1,2,4} | List2 {A,B,B,D} | List3 {W,X,Z,Z}|
    Row 3 - Text | List1 {1,1,2,4} | List2 {A,B,B,D} | List3 {W,X,Z,Z}|

    When I use Table.ExpandListColumn on the first column (List1), it adds rows to the table. Row1 is expanded into four rows, which is fine but I can only do this one at a time resulting in too many rows being added.  Row 1 is expanded as:

    Text | 1 | List2 {A,B,B,D} | List3 {W,X,Z,Z}
    Text | 1 | List2 {A,B,B,D} | List3 {W,X,Z,Z}
    Text | 2 | List2 {A,B,B,D} | List3 {W,X,Z,Z}
    Text | 4 | List2 {A,B,B,D} | List3 {W,X,Z,Z}

    If you do this again for the next column (list2), it keeps adding rows.  The above example should only have 12 rows. A single row needs to be expanded to look like:

    Text | 1 | A | W
    Text | 1 | B | X
    Text | 2 | B | Z
    Text | 4 | D | Z

    Note, the lists will always be equal in size across a starting row but may be of larger or smaller sizes and can be null.

    Wednesday, September 24, 2014 3:07 PM

Answers

  • So the values of "Text" make the rows unique? You can do the following:

    1) Start with your table with the lists not expanded.

    2) Add a custom column using the formula: Table.FromColumns({[List1],[List2],[List3]})

    3) Expand the table, selecting all columns. The 12 rows you expect will appear.

    4) Remove the list columns and rename the expanded columns.


    • Edited by Colin Banfield Wednesday, September 24, 2014 10:29 PM
    • Marked as answer by Madmoxy Friday, September 26, 2014 3:50 PM
    Wednesday, September 24, 2014 10:25 PM
  • You can account for nulls by testing to see if there is a list in the columns, but it would be cleaner to do this using a custom function e.g.

    Create a custom function (query) called TableFromLists, The code would be:

    (value1, value2, value3)=>
    let
        List1 = if Value.Is(value1, type {list}) then value1
         else {value1},
        List2 = if Value.Is(value2, type {list}) then value2
         else {value2},
        List3 = if Value.Is(value3, type {list}) then value3
         else {value3},
        Table = Table.FromColumns({List1,List2,List3})
    in
        Table

    In the code, you test to see if each input value is a list. If so, then each identifier (List1...List3) is assigned the corresponding input value (value1...value3). If not, each identifier is assigned the input value converted to a list (by surrounding the input value in braces).

     Then, in the previous step 2, the formula for the custom column would be:

    TableFromLists([List1],[List2],[List3])

    • Marked as answer by Madmoxy Friday, September 26, 2014 3:50 PM
    Thursday, September 25, 2014 2:11 AM

All replies

  • There are more than 12 unique values in the combination of lists 1, 2, & 3. What values would you expect in the 12 rows (or the next 8, since you provided the first 4)?
    Wednesday, September 24, 2014 8:16 PM
  • So each list value has a corresponding value in that row. It's the value in the same position in the other lists. So when Row 1 is expanded, the value "1" (List 1), " A" (List 2) and "W" (List 3) are all related and on a new single row. So on and so forth.

    Sorry if the explanation is confusing...I'm finding it hard to word and will do my best. The whole thing from all three rows would look like:

    Text | 1 | A | W
    Text | 1 | B | X
    Text | 2 | B | Z
    Text | 4 | D | Z
    Text | 1 | A | W
    Text | 1 | B | X
    Text | 2 | B | Z
    Text | 4 | D | Z
    Text | 1 | A | W
    Text | 1 | B | X
    Text | 2 | B | Z
    Text | 4 | D | Z


    • Edited by Madmoxy Wednesday, September 24, 2014 9:26 PM Fixed my error.
    Wednesday, September 24, 2014 9:09 PM
  • So the values of "Text" make the rows unique? You can do the following:

    1) Start with your table with the lists not expanded.

    2) Add a custom column using the formula: Table.FromColumns({[List1],[List2],[List3]})

    3) Expand the table, selecting all columns. The 12 rows you expect will appear.

    4) Remove the list columns and rename the expanded columns.


    • Edited by Colin Banfield Wednesday, September 24, 2014 10:29 PM
    • Marked as answer by Madmoxy Friday, September 26, 2014 3:50 PM
    Wednesday, September 24, 2014 10:25 PM
  • This worked!  You're correct the "Text" makes the rows unique. However, I have one unexpected issue.  Some of the rows do not have lists in the three columns. I still want to keep those rows but I can't expand the custom column because it says:

    Expression.Error: Cannot convert the value null to type List.
    Details:
        Value=
        Type=Type

    It actually starts like this:

    Row 1 - Text | List1 {1,1,2,4} | List2 {A,B,B,D} | List3 {W,X,Z,Z}|
    Row 2 - Text | null | null | null|
    Row 3 - Text | List1 {1,1,2,4} | List2 {A,B,B,D} | List3 {W,X,Z,Z}|
    Row 4 - Text | List1 {1,1,2,4} | List2 {A,B,B,D} | List3 {W,X,Z,Z}|

    and should end up like:

    Text | 1 | A | W
    Text | 1 | B | X
    Text | 2 | B | Z
    Text | 4 | D | Z
    Text |    |    |   
    Text | 1 | A | W
    Text | 1 | B | X
    Text | 2 | B | Z
    Text | 4 | D | Z
    Text | 1 | A | W
    Text | 1 | B | X
    Text | 2 | B | Z
    Text | 4 | D | Z

    Wednesday, September 24, 2014 10:55 PM
  • You can account for nulls by testing to see if there is a list in the columns, but it would be cleaner to do this using a custom function e.g.

    Create a custom function (query) called TableFromLists, The code would be:

    (value1, value2, value3)=>
    let
        List1 = if Value.Is(value1, type {list}) then value1
         else {value1},
        List2 = if Value.Is(value2, type {list}) then value2
         else {value2},
        List3 = if Value.Is(value3, type {list}) then value3
         else {value3},
        Table = Table.FromColumns({List1,List2,List3})
    in
        Table

    In the code, you test to see if each input value is a list. If so, then each identifier (List1...List3) is assigned the corresponding input value (value1...value3). If not, each identifier is assigned the input value converted to a list (by surrounding the input value in braces).

     Then, in the previous step 2, the formula for the custom column would be:

    TableFromLists([List1],[List2],[List3])

    • Marked as answer by Madmoxy Friday, September 26, 2014 3:50 PM
    Thursday, September 25, 2014 2:11 AM
  • Thank you, this worked perfectly.
    Friday, September 26, 2014 3:50 PM
  • I'm getting and error 

    Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

    Thursday, June 30, 2016 12:23 AM
  • Can you share the code that you're using? I can barely remember providing a solution to the original post.
    Thursday, June 30, 2016 4:33 AM
  • Is there a way to expand this solution to work for a dynamic number of columns?
    Sunday, March 26, 2017 2:47 AM
  • The function below determines which columns are nested lists and expand these according to the requirement in this topic. It will also work if there are no columns with nested lists or if there are only columns with nested lists. 

    (Table1 as table) as table => 
    let
        Source = Table1,
        TableSchema = Table.Schema(Source),
        ColumnNames = Table.SelectColumns(TableSchema,{"Name"}),
        IsListColumn = Table.AddColumn(ColumnNames, "IsListColumn?", each List.AllTrue(List.Transform(Table.Column(Source,[Name]), each _ is list))),
        NonListColumns = Table.SelectRows(IsListColumn, each ([#"IsListColumn?"] = false)),
        NonListColumnNames = Table.RemoveColumns(NonListColumns,{"IsListColumn?"})[Name],
        SelectNonListColumns = Table.SelectColumns(Source,NonListColumnNames),
        ListColumns = Table.SelectRows(IsListColumn, each ([#"IsListColumn?"] = true)),
        ListColumnNames = Table.RemoveColumns(ListColumns,{"IsListColumn?"})[Name],
        SelectListColumns = Table.SelectColumns(Source,ListColumnNames),
        TableFromLists = Table.AddColumn(SelectListColumns, "TableFromLists", each Table.FromColumns(Record.FieldValues(_))),
        ListTables = Table.SelectColumns(TableFromLists,{"TableFromLists"}),
        Custom1 = Table.FromColumns({Table.ToRecords(SelectNonListColumns),Table.ToRecords(ListTables)}),
        #"Expanded Column1" = Table.ExpandRecordColumn(Custom1, "Column1", Table.ColumnNames(#table(List.Min({1,List.Count(NonListColumnNames)}),{})), NonListColumnNames),
        #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column2", {"TableFromLists"}, {"TableFromLists"}),
        #"Expanded TableFromLists" = Table.ExpandTableColumn(#"Expanded Column2", "TableFromLists", Table.ColumnNames(#table(List.Count(ListColumnNames),{})), ListColumnNames),
        #"Reordered Columns" = Table.ReorderColumns(#"Expanded TableFromLists",ColumnNames[Name])
    in
        #"Reordered Columns"

    Sunday, March 26, 2017 8:47 PM
  • Thanks so much the is the only solution I have found that works for this situation and is so easy. You saved me hours fo work.
    Tuesday, August 14, 2018 12:01 PM
  • Hi Marcel,

    could you please have a look into my issue with your solution - I  am getting an error as below (or so - manually translated, while I am using Polish version):
    Expression.Error: We expected the parameter newColumnNames will have the same number of elements as  fieldNames.
    Details:
        List

    I get this at the Expanded Column1 step.

    Thank you in advance.

    Paweł

    Thursday, February 28, 2019 2:05 PM