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!

 

[scottsen]

****, so I can't pass "1=1; DROP TABLE table?"

You can certainly do some dynamic filtering in PQ (eg: Using dynamic parameter values in Power Query Queries « ORAYLIS Blog) but ... you gotta get them in there someway. Maybe a CSV file?

 

[StaticVoid]

****, so I can't pass "1=1; DROP TABLE table?"

You can certainly do some dynamic filtering in PQ (eg: Using dynamic parameter values in Power Query Queries « ORAYLIS Blog) but ... you gotta get them in there someway. Maybe a CSV file?

Thank you for answering. Yeah, I don't have to worry about SQL injections. And it is going to be used by qualified personnel only.

I thought about external text files, but I would prefer to contain everything in a workbook.
There seems to be no limit on how many characters a PQ cell contains. I entertained the idea to write vertical concatenation function in Power Query's M language. Can I somehow loop through column of text data and concatenate it with separators? I could not find it, so I wrote an ugly recursive function:

let
     recConcat = (col, sep) =>
let
       nrows = List.Count(col),
       recConcat2 = (col, idx, sep, nrows) => 
    if (nrows - 1) > idx then col{idx} & @recConcat2(col, (idx + 1), sep, nrows) & sep
    else col{idx},
       Source = recConcat2(col, 0, sep, nrows)
in
      Source
in
    recConcat

Invoking it like that:

let
    Source = colConcatenate(Excel.CurrentWorkbook(){[Name="Src"]}[Content][Header], ",")
in
    Source

It is my first M code ever, and I did something wrong here, because I'm getting NullReferenceException.
It doesn't seem I can attach files here, so here's the link to minimal example (no macros)

 

[scottsen]

Hardcore

Maybe you can Pivot the data in PQ, then append via columns on 1 row? I dunno that "sounds easier", but then... I don't spend a ton of time in PQ. We could always summon Chris Webb, that worked last time...

 

[StaticVoid]

Maybe you can Pivot the data in PQ, then append via columns on 1 row?

Could you please elaborate? I've no idea how this can be done.

We could always summon Chris Webb, that worked last time...

*Draws a circle and starts chanting.

 

[scottsen]

Chris took the last one, lemme try to summon Ken.

My theory was to use the "transpose" function, then use the Merge Columns function -- but I'm not skilled in the underlying M language to say "use whatever columns magically appeared after the transpose" or "all columns that are called FOO" or similiar. And since you will have a variable number of rows... you will end with a variable number of columns that I don't know the best way to reference in your merge columns.

So, let's make Ken help us, and if that fails we will get Chris

 

[StaticVoid]

scottsen, you're so awesome, thank you!

 

[Ken Puls]

Hi there,

"I'm looking for a way to pass a VBA string variable to PQ"

Unfortunately you can't do that, but one thing you could do is build your base query using VBA, inject it into a txt file, then load the contents of that file for your Power Query script. Not sure if that would help, but it may.

I'm still trying to follow exactly what you're tying to build here... do you have a sample of what your code looks like? (I tend to work better when I can get my hands on sample data...)

 

[StaticVoid]

Hello Ken, thank you for answering.
Since the VBA function only concatenates a column of text cells into one string with a separator, I have tried to implement this in PQ's M language, in order to avoid using external text files. I have uploaded the minimal example and provided the link above (click me!).

 

[Ken Puls]

Hey there,

My head is just stuffed with a cold, so apologies in advance if I'm not following, but try this. 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,,,,,,,,

This should return: a,b,c,d,e,f,g,h

It should also be dynamic to pick up as many columns as are in there:

let
    Source = Excel.CurrentWorkbook(){[Name="Src"]}[Content],
    Transposed = Table.Transpose(Source),
    Head = Table.ColumnNames(Transposed),
    Merged = Table.CombineColumns(Transposed,Head,Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    Merged

I'm hoping that's what you're after, but if not, maybe circle back and re phrase it a bit. I'm not totally following from the original post if I'm on the right track.