Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lazarys
Regular Visitor

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

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?

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

 

View solution in original post

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

Hi @lazarys ,

 

Have you tried merge queries in Power Query?

Merge queries overview - Power Query | Microsoft Docs

vstephenmsft_1-1650593887540.png

 

 

 

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.

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors