Forum Discussion

Anonymous's avatar
Anonymous
Aug 01, 2019

Power 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...
excel
Anonymous's avatar
Anonymous
Aug 03, 2019
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.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 04, 2019

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. 

Resources