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

[LeaningTree]

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 

[Phil_Seamark]

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.  

[ImkeF · Accepted solution]

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

[LeaningTree]

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.   

[Phil_Seamark]

Hi @ImkeF

That's pretty cool.