How to control data amount on PBI desktop and service using PowerQuery parameter

[PbiCeo]

Hello experts,

I want to create a report getting data from Azure SQL DB, but the data amount is quite large and PBI desktop is often frozen.
So I want to get data only for the past 2 months on PBI desktop using PowerQuery parameter (Date),
and then publish it to service and edit the parameter for all time.
Is it possible to do that?
If so, could you provide an example.
Thanks!
Vladi

[rohit_singh · Accepted solution]

Hi @PbiCeo ,

You can try something like this :

1) Create a start date parameter called 'dateStart' by creating a new query and adding the following code:
Date.AddMonths(Date.From(DateTime.FixedLocalNow()),-2).

This will give you a dynamic start date that is 2 months before current date.

2) In your Azure SQL query, you can then add a step to filter using dateStart as

This should give you a filtered dataset with only 2 months worth of data.

Based on what you ask is, you can probably create another parameter named 'months' and use it as 
Date.AddMonths(Date.From(DateTime.FixedLocalNow()),months), keeping the initial value as -2 and then changing the value in the Paramters section in the dataset settings on the workspace, if possible.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂