Consolidating multiple SAP BW queries, with parameters from excel sheet into a single resultset

[lazarys]

I have a report that runs a number of different SAP BW queries, (each with a distinct but varying set of parameters, some as "*" wildcards). I have put the input requirements (Lets call that table "REQUIREMENTS") into an excel table. To that I have added a column that based on the parameters, indicates which of four BW Queries should be run with the indicated parameters (GL303_BASE,GL303_DT,GL303_ORDERS, and GL303_DT_ORDERS.) I have also created four PowerBI functions that return the resultset of each query (see above), given the parmeter set. I have not found how to loop through the driving "REQUIREMENTS" table (which has multiple distinct rows for each of those query methods), calling the appropriate function for each, and then consolidating them into a single table resultset. Does anyone have an example showing this kind of thing?

[Anonymous]

Hi @lazarys ,

Have you tried merge queries in Power Query?

Merge queries overview - Power Query | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lazarys · Accepted solution]

Hi Stephen -

I hadn't tried merge-queries, because I was still trying to get an excel spreadsheet to drive the multiple dynamic queries to SAP business warehouse. Another part of the issue was the dreaded "formula firewall".  I eventually solved it by separating the connection portion of the datacube from the retrieve data portion, putting the retrieve data into a custom function that returned a dataset depending on what parameters were passed. I was then able to read the requirement excel spreadsheet, and add a column invoking the custom function for each line with the specific parameters and get that table back for each line. Finally using Table.Expand TableColumn to blow the list up to all the records in one dataset worked very neatly.  

Thank you for looking into this for me.