Forum Discussion
Deleted
Aug 01, 2019Power Query - String as Table name
Hi, In Power Query how can I call a Table name in a function by using a concatenated text sting. e.g : Table1 = Table.FromRows({{1,2}},{"a","b"}), Sufix = "Table" Index = "1" I want to refer to Table1...
Wyn Hopkins
Aug 03, 2019MVP
Hi Deleted
Do you mean something like this...?
- DeletedAug 03, 2019Hi Wyn, thanks for your help. That works only whith the "Excel.CurrentWorkbook" function. My table isn't in a Excel Sheet. I need a query witch in is possible to select a table with a parameter as show in the eg attached. Where SelIndex is a parameter list (1,2,3). But in this eg, the query return only the table name, not the table.
- Wyn HopkinsAug 05, 2019MVP
Deleted
Here's an option
let MyTableRecord = [Table1 = Table.FromRows({{1,2}, {"a", "b"}}), Table2 = Table.FromRows({{2,3}, {"a", "b"}}), Table3 = Table.FromRows({{3,4}, {"a", "b"}}) ], TableSelect = Record.Field(MyTableRecord, "Table" & Text.From(SelIndex) ) in TableSelectI have to thank Imke https://www.thebiccountant.com/ for this approach
- DeletedAug 05, 2019Hi Wyn, Thank for this option but that's what I try to avoid. I want to extract a multi-level BOM from our PDM system in Excel. The data come from a parent/child relationship table. To do this you have to extract the BOM from the final item, then from each of its components and so on to the lowest level of the structure. Every BOM of each component can be considered as a separate table. My idea is to store each of these single tables in a list using the "List.Generate" function. Then build the arborescence of the BOM, by structuring the tables of this list. I have tried to understand the "List.Generate" function. It is quite simple to produce a list of "Records". But if converting a three rows and two columns table into "Record" is simple, it is much harder when you have dozens of rows and columns. The problem is that I don't know how to index the tables like it's possible for "Records".
- SergeiBaklanAug 04, 2019Diamond Contributor
Deleted
I'm not sure it's possible to make reference such way on any name, but it's possible to choose from predefined names, like
let nIndex = 2, Table1 = Table.FromRows({{1,2}},{"a","b"}), Table2 = Table.FromRows({{3,4}},{"a","b"}), Table3 = Table.FromRows({{5,6}},{"a","b"}), // Select from environment TableSelect = Expression.Evaluate("Table" & Number.ToText(nIndex), [ Table1=Table1, Table2=Table2, Table3=Table3 ] ), // Select by function, but actually it's the same TableSeletByFunction=SelectMyTable(3), SelectMyTable = let fnSelectTbl = (n as number) => let tables = { {1,Table1}, {2,Table2}, {3,Table3} }, ReturnTable = List.First(List.Select(tables, each _{0}=n)){1} in ReturnTable in fnSelectTbl in TableSelect //TableSeletByFunctionIf Table(i) are returned by another quires we may use #shared environment, when it's not necessary to predefine the list of existing tables.
- DeletedAug 06, 2019Thanks Sergei, but I was looking for a simple way to lighten my code.
But I keep this solution, it could be useful.
I had tried with the "Expression.Evaluate" function. But I admit that I did not fully understand the concept of "environment".
- Wyn HopkinsAug 04, 2019MVPAh, ok. Not an easy answer. Can you expand on why you want to do this and I may have an alternative approach