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
LeaningTree
New Member

Use DAX Output to generate parameters for Power Query like you can with Excel tables?

I have several very large CSV files that I am processing using Power Query in Power BI Dekstop, and previously in Excel Power Pivot I was able to use tables that I output with DAX Evaluation statements as parameters for input to Power Query.  I did this to avoid re-ingesting the entire CSVs using Power Query to identity things like Min/Max Date for calendar tables, identity distinct grouping output for columns that will be the one side of relationships and a number of other operations.  I did this because these operations are very quick to perform when data is accessible via DAX from a data model table, but very time consuming to output from Power Query (grouping or other operations that require scanning the entire CSV file) because it results in reloading the entire enormous CSV files again.

 

Is there a way to use output from a DAX table of your original input as a parameter for Power Query in Power BI Desktop?  I saw that you can generate some items as Parameter Queries, but they seemed to all involve reloading your entire original dataset through a separate query instead of accessing a parameter from within an existing Power Query script or an output from another part of your data model?  Is there any way to accomplish this trick using Power BI Desktop, verus having to use Excel tables built from DAX as your Power Query parameters?  I feel like there shoudl be a way to reference tables in Power BI Desktop similiarly, but don't see it.  

 

Thank you 

1 ACCEPTED SOLUTION

4 REPLIES 4
Phil_Seamark
Employee
Employee

hi @LeaningTree

 

Not easily.  You can hack a connection of Power Query to the underlying Power BI Desktop model using port numbers etc, but these change everytime you close/open your PBIX file.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Did you check this method?: http://www.thebiccountant.com/2017/03/22/use-slicers-for-query-parameters-in-powerbi/

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thank you very much for that reference.  I have not tried that and will definitely go through the details you provided - that is more or less exactly what I was looking for (and it also provides the added potential for a user to select values - which I am sure will come up sooner or later as well).  

 

I believe that I previously used a similar solution to access the Power BI Data Model from Excel so that I could generate basic pivotable output from a Power BI solution (I believe that I found that data on your site and I provided a PowerShell script to identify the Power BI system ID).  I should have remembered to check your site!  This is incredibly helpful, hopefully I can make it work for my data model.

 

I hope that the Power BI developers will consider enabling this type of solution through the query parameter process at some point in the future.  Having to reload massive files through Power Query versus manage more efficient options to group/categorize data with DAX to use as parameters for other queries is a significant limitation in cases when the luxury of a backend database source for some of the large files you are dealing managing.   

Hi @ImkeF

That's pretty cool.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.