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 expand only certain specific column indexes (I can't use column names because they change from month to month).

The code I have so far looks like this:

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

 

However, this results in receiving this message:

Expression.Error: The field 'Project ID' already exists in the record.
Details:
Name=Project ID
Value=

 

(Note: "Project ID" is my first column. )

I have also tried the above code, but starting from 1 instead of 0, with the same result.

Does anyone have any ideas what I'm doing wrong? Thanks in advance! 🙂

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]){_})
    )

2 Replies

  • 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.