Forum Discussion
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
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
- SergeiBaklanDiamond Contributor
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.
- AnkitaMCopper ContributorThank you SergeiBaklan. It worked.
- SergeiBaklanDiamond Contributor
AnkitaM , you are welcome