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

[Gabe_V]

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! 🙂

[wdx223_Daniel · Accepted solution]

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]

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.