Pass global VBA variable to Power Query

[StaticVoid]

Hello!

I have a query to pass to mySQL database through Power Query. In essence, it looks like this:

SELECT * 
FROM table 
WHERE 
col1 LIKE "%abc%" OR
col1 LIKE "%xyz%" OR
....

It is a part of more complex query that joins several tables . In my workbook I have a list of names ("abc", "xyz" etc.). Loading entire table and matching it in power query is not an option, because it is too large.

My idea was to concatenate the names in one cell, make a dynamic range and refer them in PowerQuery:

QueryPart = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content][MyColumn]{0}
Source = MySQL.Database("server", "database", [Query="SELECT * FROM table WHERE " & QueryPart])

It is secure since database only allows select queries.

The problem is that concatenated names often exceed maximum number of characters allowed in Excel cell, so I'm looking for a way to pass a VBA string variable to PQ. Any alternative solutions are welcome as well.

I'm using Excel 2010 Pro Plus.

Thank you!

 

[StaticVoid]

Sorry for posting in the wrong thread. Didn't notice Power BI has its own. Reported to moderators.

 

[miguel.escobar]

what do you mean by "loading entire table"? I'm not following correctly but I think I get some idea. You want the user to select something (still not sure what) and then pass that input as part of something that will run in Power Query. You could concatenate the name in Power Query and not in Excel itself but perhaps you're refering to that QueryPart that perhaps is too long for Excel and you want it as your input?