Forum Discussion

AnkitaM's avatar
AnkitaM
Copper Contributor
Oct 13, 2020
Solved

Reference to a Query (connection only) based on a string

Hello everyone, 

 

I am trying to use Power Query in the excel. I have to import a table from any of the 3 different existing data sources which are connection only queries. I am trying to select the connection on the excel and import the table based on that value. 

 

For example, there are 3 connection only queries  - Customer, Supplier, Internal which have data in similar format. The table where I need them is say Networks. 

 

Now I am able to create a string like #"Customer" based on what is selected in the excel but I am unable to use this information as a source in my query for Networks. 

 

I tried this: 

let
WLTable="#"&""""&fnGetParameter("Network Group")&"""",
Source = WLTable,
#"Added Custom" = Table.AddColumn(Source, "Date Refreshed", each Date.From(DateTime.LocalNow())),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"NETWORK", type text}, {"Date Refreshed", type date}})
in
#"Changed Type"

 

But it gives me error: 

Expression.Error: We cannot convert the value "#"Customer Specific ..." to type Table.
Details:
Value=#"Customer Specific WL"
Type=[Type]

 

The function fnGetParameter is supposed to get the connection name from the excel and that part is working fine.  It is the part where this should be referred as a query or table is where there seems to be some problem. 

 

Can anyone guide me how to go about it. 

 

Thanks in advance! 

AM

  • AnkitaM 

    Function returns some text, perhaps #"Customer Specific WL".  Source also will be exactly the same text value. Assuming that is correct table name, you may use

    Source = Expression.Evaluate(WLTable,#shared),
    ...

    to evaluate it in current environment (#shared) and return table.

     

    More about this function is here Expression.Evaluate() In Power Query/M  That is relatively old blog but still actual.

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    AnkitaM 

    Function returns some text, perhaps #"Customer Specific WL".  Source also will be exactly the same text value. Assuming that is correct table name, you may use

    Source = Expression.Evaluate(WLTable,#shared),
    ...

    to evaluate it in current environment (#shared) and return table.

     

    More about this function is here Expression.Evaluate() In Power Query/M  That is relatively old blog but still actual.

Resources