You can use the INDIRECT function.How it's possible to retrieve column names as a list from a list of tables?
View attachment 121690
Sample Data.xlsm | |||||||
---|---|---|---|---|---|---|---|
D | E | F | G | H | |||
3 | |||||||
4 | tblWeights | Weight Unit, Abbreviation | |||||
5 | tblCheeses | Food Type, Cheese | |||||
6 | tblFoodItem | Food Type, Food Item | |||||
7 | |||||||
8 | tblWeights | Weight Unit | Abbreviation | ||||
9 | tblCheeses | Food Type | Cheese | ||||
10 | tblFoodItem | Food Type | Food Item | ||||
11 | |||||||
Weights |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F4:F6 | F4 | =TEXTJOIN(", ",TRUE,INDIRECT(E4 & "[#Headers]")) |
F8:G10 | F8 | =INDIRECT(E8 & "[#Headers]") |
Dynamic array formulas. |
Thanks but I need to do this particularly in PQ to be it dynamic.You can use the INDIRECT function.
Sample Data.xlsm
D E F G H 3 4 tblWeights Weight Unit, Abbreviation 5 tblCheeses Food Type, Cheese 6 tblFoodItem Food Type, Food Item 7 8 tblWeights Weight Unit Abbreviation 9 tblCheeses Food Type Cheese 10 tblFoodItem Food Type Food Item 11 Weights
Cell Formulas Range Formula F4:F6 F4 =TEXTJOIN(", ",TRUE,INDIRECT(E4 & "[#Headers]")) F8:G10 F8 =INDIRECT(E8 & "[#Headers]") Dynamic array formulas.
For more clarity added some changes:Power Query:List.Distinct(List.Combine(List.Transform(list_of_tables, Table.ColumnNames)))