# Pass global VBA variable to Power Query



## StaticVoid (Oct 24, 2014)

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 (Oct 24, 2014)

****, 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 (Oct 24, 2014)

scottsen said:


> ****, 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 (Oct 24, 2014)

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 (Oct 24, 2014)

scottsen said:


> 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 (Oct 25, 2014)

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 (Oct 25, 2014)

*scottsen, *you're so awesome, thank you!


----------



## Ken Puls (Oct 27, 2014)

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 (Oct 28, 2014)

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 (Oct 28, 2014)

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.


----------



## StaticVoid (Oct 24, 2014)

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 (Oct 31, 2014)

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 (Oct 31, 2014)

StaticVoid said:
			
		

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


Awesome!  



			
				StaticVoid said:
			
		

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



			
				StaticVoid said:
			
		

> 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 (Nov 1, 2014)

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


----------

