Forum Discussion

Gabe_V's avatar
Gabe_V
Helper I
3 years ago
Solved

Using List.RemoveItems in Table.ExpandTableColumn results in The field already exists in the record.

Hi folks,   I have a problem that I haven't been able to track down an existing solution for. I have grouped some columns and have a single resultant aggregated column (named "All"), but I need to...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    List.RemoveItems(
    Table.ColumnNames(#"Grouped Rows"{0}[All]),
    List.Transform({0, 1, 2, 3, 4, 5, 6, 8, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33, 35, 40, 44},each Table.ColumnNames(#"Grouped Rows"{0}[All]){_})
    )

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

List.RemoveItems(
Table.ColumnNames(#"Grouped Rows"{0}[All]),
List.Transform({0, 1, 2, 3, 4, 5, 6, 8, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33, 35, 40, 44},each Table.ColumnNames(#"Grouped Rows"{0}[All]){_})
)

  • Gabe_V's avatar
    Gabe_V
    Helper I
    3 years ago

    Hi wdx223_Daniel, 

    Your solution looks very elegant and compact. However, unfortunately when I ran your solution, I think it may not be doable for very large data sets such as mine. It has been cranking away for about 12 minutes now.
    However just this morning, I came up with a less elegant solution that I got to run in about a minute:

    #"Expanded All" = Table.ExpandTableColumn(
    #"Grouped Rows",
    "All",
    List.RemoveItems(
    Table.ColumnNames(#"Grouped Rows"{0}[All]),
    {Table.ColumnNames(#"Grouped Rows"{0}[All]){0},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){1},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){2},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){3},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){4},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){5},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){6},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){8},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){13},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){15},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){17},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){19},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){21},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){23},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){25},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){27},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){29},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){31},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){33},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){35},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){40},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){44}}
    ),
    List.RemoveItems(
    Table.ColumnNames(#"Grouped Rows"{0}[All]),
    {Table.ColumnNames(#"Grouped Rows"{0}[All]){0},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){1},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){2},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){3},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){4},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){5},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){6},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){8},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){13},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){15},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){17},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){19},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){21},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){23},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){25},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){27},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){29},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){31},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){33},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){35},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){40},
    Table.ColumnNames(#"Grouped Rows"{0}[All]){44}}
    )
    )

     

    Like I said, not elegant, but it appears to do the trick.

    I will keep your solution in mind for smaller datasets though.