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]

Hello Ken. Sorry about the late response.

Your solution is exactly what I needed, thank you very much!

I've re-written your function (not a function any more), to concatenate all the columns. Interestingly, your function worked for me, but it ended up with abcdefgh,,,,,,,,

Yeah, that makes sense now that I look at it. I wonder why I'm getting Null Reference Exception though. Just out of curiosity, are you using Excel 2013 or 2010?

 

[Ken Puls]

Your solution is exactly what I needed, thank you very much!

Awesome!

I wonder why I'm getting Null Reference Exception though.

Honestly, not sure. I'd probably need to see the real data to figure that one out. I suppose it could be from having too many records (exhausting memory) or having some bad data in there. Both of those are just hypothetical though, and without seeing the real goods it's hard to know. If you want to find out, try chopping your data in half or just using 10 records to see if it persists.

Just out of curiosity, are you using Excel 2013 or 2010?

I'm using Excel 2013. I just tested it in Excel 2010 as well though. Strangely, it gagged at first, but when I refreshed it again it came through with the same result as before (abcdefgh,,,,,,,)

 

[StaticVoid]

Honestly, not sure. I'd probably need to see the real data to figure that one out.

I'm getting this Null Reference Exception on "abcdefgh" data as well. Must be some problem on Microsoft side.

it came through with the same result as before (abcdefgh,,,,,,,)

I think I got this part wrong

if (nrows - 1) > idx then col{idx} & @recConcat2(col, (idx + 1), sep, nrows) & sep

should be

if (nrows - 1) > idx then col{idx} & sep & @recConcat2(col, (idx + 1), sep, nrows)

Hard to debug when I can't see the result.

But anyway, your solution works and that's all that really matters. Thanks again, Ken.