Forum Discussion
Power Query - String as Table name
Hi Deleted
Do you mean something like this...?
- 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".
- Wyn HopkinsAug 05, 2019MVPUnfortunately I'm out of ideas. Don't know if this is relevant, but this sort of thing sounds like like the type of issue Chris Webb tackles
https://blog.crossjoin.co.uk/2013/06/22/flattening-a-parentchild-relationship-in-data-explorer/
- 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".- SergeiBaklanAug 06, 2019Diamond Contributor
Deleted , environment concept is explained, in particular, here https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/ and https://ssbi-blog.de/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power-bi-desktop-part-3/
IMHO, that could be suitable if you use external queries for your tables, otherwise simple record approach which Wyn suggested is more preferable.
- 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