Forum Discussion
Expand colum problem
I working on a project that have multi columns data from multi excel files. User must declare what columns to keep from sample fisrt N rows of data and don't care about headers, just column1, column2....Im did a table contain rows of value like 4,5,15,20...and try this code, but i dont know how to do it right.
let Cols = Table2, \\Contain rows of colums to keep Colnums = Table.ToList (Table2), Source = Table1 \\Mass data table ~50 cols #"Expanded" = Table.ExpandTableColumn(Source, "Table1", each List.Select(Colnums)) in #"Expanded"
this is my result:.........references other queries or steps, so it may not directly access a data source....How i do it right?
Great, thanks you v-lvzhan-msft, not exact like i looking for but appriciated. I made a parameter ColsToKeep = Table.ToList(tbl1) from Table1 then try #"Expanded tbl2" = Table.ExpandTableColumn(PreviousStep, "tbl2", ColsToKeep, ColsToKeep) . Worked. Thanks again.
3 Replies
Eric_ZhangMicrosoft Employee
If what you'd like is to select certain columns from one table based on the columns stored in another table, check
let Source = Table.FromRows({{1,2,3,4,5},{1,2,3,4,5},{1,2,3,4,5}},{"col1","col2","col3","col4","col5"}), columnListTable = Table.FromRows({{"col1"},{"col2"},{"col3"}},{"column"}), selectedColumns = Table.SelectColumns(Source,Table.ToList(columnListTable )) in selectedColumnsGreat, thanks you v-lvzhan-msft, not exact like i looking for but appriciated. I made a parameter ColsToKeep = Table.ToList(tbl1) from Table1 then try #"Expanded tbl2" = Table.ExpandTableColumn(PreviousStep, "tbl2", ColsToKeep, ColsToKeep) . Worked. Thanks again.
- Eric_Zhang
As your question is solved, if no further questions, you can accept your that reply as solution to close this thread. :)