Forum Discussion
Helper IVDefault Select All when extract the file combination
Dear Experts,
I have combined the different file from different sources.
When I extract those combine files in power query, I've already selected the "Select All Columns"
but when click "Load More", "Select All columns" was unchecked automatically.
The issue becomes when the new month came and combined.
The new month's data are not loaded to data model.
Please help to make "Select All Columns" checked everytime.
Thanks a lot.
BeforeAfter
Hi KyawMyoTun,
As the document mentions, the 'Table.ExpandTableColumn' include two list parameter for expanding and renaming the table columns. This part seems change dynamically on your side, if you use a static list of string values, you have to manually handle the value changes to show all the columns in your table.
In my opinion, I'd like to suggest your use the power query operator extract the list value and store it into a query step, then you can use this parameter to replace the list parameters in the 'Table.ExpandTableColumn' function.
M Language Operators - PowerQuery M | Microsoft Docs
For example:
Expand with a static list.
let Source = xxxxxx, #"Expanded Temp" = Table.ExpandTableColumn(Source, "Temp", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}) in #"Expanded Temp"Expand with parameter list.
let Source = xxxxxx, paraList= Table.ColumnNames(Source[Temp]{0}), #"Expanded Temp" = Table.ExpandTableColumn(Source, "Temp", paraList,paraList) in #"Expanded Temp"Add 'column 4' to the internal table:
Nothing changes
New column auto added
You can add a custom step before the 'expand column' step to extract the parameter list.
Sample formulas:paraList= Table.ColumnNames(#"Renamed Other Columns1"["Transform File (5)"]{0}), #"Expanded Transform File (5)" = Table.ExpandTableColumn(#"Renamed Other Columns1", "Transform File (5)", paraList, paraList)Regards,
Xiaoxin Sheng
5 Replies
- lbendlin
Super User
Use the Advanced Editor and remove the column count reference.
KyawMyoTunDear lbendlin ,
Here is my query steps.
Can you please guide me through to solve this?
Thanks.
Hi KyawMyoTun,
Perhaps you can try to create a query step as a parameter to extract and store the field values, then you can use this parameter in your expand function instead of static text values. It will update every time your table records change and not need you to manually handle this.
Regards,
Xiaoxin Sheng
- KyawMyoTun
Dear Anonymous ,
Can yo please help me to create those kind of parameters?
Thanks.Hi KyawMyoTun,
As the document mentions, the 'Table.ExpandTableColumn' include two list parameter for expanding and renaming the table columns. This part seems change dynamically on your side, if you use a static list of string values, you have to manually handle the value changes to show all the columns in your table.
In my opinion, I'd like to suggest your use the power query operator extract the list value and store it into a query step, then you can use this parameter to replace the list parameters in the 'Table.ExpandTableColumn' function.
M Language Operators - PowerQuery M | Microsoft Docs
For example:
Expand with a static list.
let Source = xxxxxx, #"Expanded Temp" = Table.ExpandTableColumn(Source, "Temp", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}) in #"Expanded Temp"Expand with parameter list.
let Source = xxxxxx, paraList= Table.ColumnNames(Source[Temp]{0}), #"Expanded Temp" = Table.ExpandTableColumn(Source, "Temp", paraList,paraList) in #"Expanded Temp"Add 'column 4' to the internal table:
Nothing changes
New column auto added
You can add a custom step before the 'expand column' step to extract the parameter list.
Sample formulas:paraList= Table.ColumnNames(#"Renamed Other Columns1"["Transform File (5)"]{0}), #"Expanded Transform File (5)" = Table.ExpandTableColumn(#"Renamed Other Columns1", "Transform File (5)", paraList, paraList)Regards,
Xiaoxin Sheng
