SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1774064%22%20slang%3D%22en-US%22%3EReference%20to%20a%20Query%20(connection%20only)%20based%20on%20a%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1774064%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20Power%20Query%20in%20the%20excel.%20I%20have%20to%20import%20a%20table%20from%20any%20of%20the%203%20different%20existing%20data%20sources%20which%20are%20connection%20only%20queries.%20I%20am%20trying%20to%20select%20the%20connection%20on%20the%20excel%20and%20import%20the%20table%20based%20on%20that%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20there%20are%203%20connection%20only%20queries%26nbsp%3B%20-%20Customer%2C%20Supplier%2C%20Internal%20which%20have%20data%20in%20similar%20format.%20The%20table%20where%20I%20need%20them%20is%20say%20Networks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20am%20able%20to%20create%20a%20string%20like%20%23%22Customer%22%20based%20on%20what%20is%20selected%20in%20the%20excel%20but%20I%20am%20unable%20to%20use%20this%20information%20as%20a%20source%20in%20my%20query%20for%20Networks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3Elet%3CBR%20%2F%3EWLTable%3D%22%23%22%26amp%3B%22%22%22%22%26amp%3BfnGetParameter(%22Network%20Group%22)%26amp%3B%22%22%22%22%2C%3CBR%20%2F%3ESource%20%3D%20WLTable%2C%3CBR%20%2F%3E%23%22Added%20Custom%22%20%3D%20Table.AddColumn(Source%2C%20%22Date%20Refreshed%22%2C%20each%20Date.From(DateTime.LocalNow()))%2C%3CBR%20%2F%3E%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Added%20Custom%22%2C%7B%7B%22NETWORK%22%2C%20type%20text%7D%2C%20%7B%22Date%20Refreshed%22%2C%20type%20date%7D%7D)%3CBR%20%2F%3Ein%3CBR%20%2F%3E%23%22Changed%20Type%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20gives%20me%20error%3A%26nbsp%3B%3C%2FP%3E%3CP%3EExpression.Error%3A%20We%20cannot%20convert%20the%20value%20%22%23%22Customer%20Specific%20...%22%20to%20type%20Table.%3CBR%20%2F%3EDetails%3A%3CBR%20%2F%3EValue%3D%23%22Customer%20Specific%20WL%22%3CBR%20%2F%3EType%3D%5BType%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20function%26nbsp%3BfnGetParameter%20is%20supposed%20to%20get%20the%20connection%20name%20from%20the%20excel%20and%20that%20part%20is%20working%20fine.%26nbsp%3B%20It%20is%20the%20part%20where%20this%20should%20be%20referred%20as%20a%20query%20or%20table%20is%20where%20there%20seems%20to%20be%20some%20problem.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20guide%20me%20how%20to%20go%20about%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%26nbsp%3B%3C%2FP%3E%3CP%3EAM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1774064%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

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

3 Replies
Highlighted
Best Response confirmed by AnkitaM (New Contributor)
Solution

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

Highlighted
Highlighted