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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors