Forum Discussion
Helper IIHow to control data amount on PBI desktop and service using PowerQuery parameter
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
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 asThis 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! 🙂
1 Reply
rohit_singhSolution Sage
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 asThis 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! 🙂