Home

Power Query - String as Table name

%3CLINGO-SUB%20id%3D%22lingo-sub-784006%22%20slang%3D%22fr-FR%22%3EPower%20Query%20-%20String%20as%20Table%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-784006%22%20slang%3D%22fr-FR%22%3EHi%2C%20In%20Power%20Query%20how%20can%20I%20call%20a%20Table%20name%20in%20a%20function%20by%20using%20a%20concatenated%20text%20sting.%20e.g%3A%20Table1%20-%20Table.FromRows(%22a%2C%22%22b%22)%2C%20Sufix%20-%20%22Table%22%20Index%20-%20%221%22%20I%20want%20to%20refer%20to%20Table1%20by%20using%20the%20string%20Suffix%20-%20Index%20or%20something%20like%20that.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-784006%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786668%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20String%20as%20Table%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786668%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379939%22%20target%3D%22_blank%22%3E%40Lallou%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20mean%20something%20like%20this...%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125496i7DBCD6164B0D772F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786736%22%20slang%3D%22fr-FR%22%3ERe%3A%20Power%20Query%20-%20String%20as%20Table%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786736%22%20slang%3D%22fr-FR%22%3EHi%20Wyn%2C%20thanks%20for%20your%20help.%20That%20works%20only%20whith%20the%20%22Excel.CurrentWorkbook%22%20function.%20My%20table%20isn't%20in%20an%20Excel%20Sheet.%20I%20need%20a%20query%20witch%20in%20is%20possible%20to%20select%20a%20table%20with%20a%20parameter%20as%20show%20in%20the%20eg%20attached.%20Where%20SelIndex%20is%20a%20parameter%20list%20(1%2C2%2C3).%20But%20in%20this%20eg%2C%20the%20query%20return%20only%20the%20table%20name%2C%20not%20the%20table.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-787128%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20String%20as%20Table%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-787128%22%20slang%3D%22en-US%22%3EAh%2C%20ok.%20Not%20an%20easy%20answer.%20Can%20you%20expand%20on%20why%20you%20want%20to%20do%20this%20and%20I%20may%20have%20an%20alternative%20approach%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-787330%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20String%20as%20Table%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-787330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379939%22%20target%3D%22_blank%22%3E%40Lallou%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20it's%20possible%20to%20make%20reference%20such%20way%20on%20any%20name%2C%20but%20it's%20possible%20to%20choose%20from%20predefined%20names%2C%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3Elet%0A%20%20%20%20nIndex%20%3D%202%2C%0A%20%20%20%20Table1%20%3D%20Table.FromRows(%7B%7B1%2C2%7D%7D%2C%7B%22a%22%2C%22b%22%7D)%2C%0A%20%20%20%20Table2%20%3D%20Table.FromRows(%7B%7B3%2C4%7D%7D%2C%7B%22a%22%2C%22b%22%7D)%2C%0A%20%20%20%20Table3%20%3D%20Table.FromRows(%7B%7B5%2C6%7D%7D%2C%7B%22a%22%2C%22b%22%7D)%2C%0A%0A%20%20%20%20%2F%2F%20Select%20from%20environment%0A%20%20%20%20TableSelect%20%3D%20Expression.Evaluate(%22Table%22%20%26amp%3B%20Number.ToText(nIndex)%2C%0A%20%20%20%20%20%20%20%20%5B%0A%20%20%20%20%20%20%20%20Table1%3DTable1%2C%0A%20%20%20%20%20%20%20%20Table2%3DTable2%2C%0A%20%20%20%20%20%20%20%20Table3%3DTable3%0A%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20)%2C%0A%0A%20%20%20%20%2F%2F%20Select%20by%20function%2C%20but%20actually%20it's%20the%20same%0A%20%20%20%20TableSeletByFunction%3DSelectMyTable(3)%2C%0A%0ASelectMyTable%20%3D%0A%20%20%20%20let%20fnSelectTbl%20%3D%20(n%20as%20number)%20%3D%26gt%3B%0A%20%20%20%20%20%20%20%20let%0A%20%20%20%20%20%20%20%20%20%20%20%20tables%20%3D%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B1%2CTable1%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B2%2CTable2%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B3%2CTable3%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ReturnTable%20%3D%20List.First(List.Select(tables%2C%20each%20_%7B0%7D%3Dn))%7B1%7D%0A%20%20%20%20%20%20%20%20in%0A%20%20%20%20%20%20%20%20%20%20%20%20ReturnTable%0A%20%20%20%20in%0A%20%20%20%20%20%20%20%20fnSelectTbl%0Ain%0A%20%20%20%20TableSelect%20%2F%2FTableSeletByFunction%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20Table(i)%20are%20returned%20by%20another%20quires%20we%20may%20use%20%23shared%20environment%2C%20when%20it's%20not%20necessary%20to%20predefine%20the%20list%20of%20existing%20tables.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-787570%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20String%20as%20Table%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-787570%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379939%22%20target%3D%22_blank%22%3E%40Lallou%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere's%20an%20option%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%0A%20%20%20MyTableRecord%20%3D%20%5BTable1%20%3D%20Table.FromRows(%7B%7B1%2C2%7D%2C%20%7B%22a%22%2C%20%22b%22%7D%7D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table2%20%3D%20Table.FromRows(%7B%7B2%2C3%7D%2C%20%7B%22a%22%2C%20%22b%22%7D%7D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table3%20%3D%20Table.FromRows(%7B%7B3%2C4%7D%2C%20%7B%22a%22%2C%20%22b%22%7D%7D)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20TableSelect%20%3D%20Record.Field(MyTableRecord%2C%20%22Table%22%20%26amp%3B%20Text.From(SelIndex)%20)%0Ain%0A%20%20%20TableSelect%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20to%20thank%20Imke%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.thebiccountant.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.thebiccountant.com%2F%3C%2FA%3E%26nbsp%3Bfor%20this%20approach%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable
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 by using the string Suffix & Index or something like that.
11 Replies

Hi @Deleted

 

Do you mean something like this...?

 

clipboard_image_0.png

 

Hi 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.
Ah, ok. Not an easy answer. Can you expand on why you want to do this and I may have an alternative approach

@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 //TableSeletByFunction

If Table(i) are returned by another quires we may use #shared environment, when it's not necessary to predefine the list of existing tables. 

@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
   TableSelect

 

I have to thank Imke https://www.thebiccountant.com/ for this approach

 

 

Hi 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".
Unfortunately 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/
Thanks Wyn, but that's only useful after extracting the data. I have first to find the good query. But I will try some different ways. I keep you informed.
Thanks 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".

@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.

Thanks Sergei for the links.