Can Power Query [M] determine its run environment?

Question

I'm using Power Query to bring together about 300 Excel documents, each with many sheets. There are so many data items that a full refresh in the Power BI Desktop takes too long for efficient development, so I use a Keep Top 5 rows step to cut down the bulk of data for these purposes.

Once testing of a change has been completed though, I want to publish to the Power BI Service a version of the model with no filter and schedule an overnight run.

To simplify deploy processes, I'm looking for a way to leave a permanent line of code in the query that will restrict to only the top 5 rows when running on the Desktop, but use all of the data if running on the Service. Is such a thing possible?

I've added this as an idea on the Power BI website - so if this is new then please vote it up https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20439535-enviroment-sensitive-m

Thanks but my question is whether that variable can come from the run environment. (i.e. "Desktop" vs "Excel 2016" vs "SQL Server 2017" vs "PBI Service") something like that. — Richard Boland

Mike Honey Mike Honey · Accepted Answer · 2017-09-07T07:35:43

I would create a Parameter for the number of Rows, then apply that in Keep Rows steps on each Query. You can create the Step using the UI, then edit it to substitute the Parameter, e.g.

= Table.FirstN(#"My Previous Step", #"My Rows to Keep Parameter" )

When you Publish to app.powerbi.com, it locks in the current setting of each Parameter. So just before you publish, change it's value to a very big number.

Can Power Query [M] determine its run environment?

1 Answers