Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Is there any way to change/update Data source dates (from date - to date) dynamically every month end to fetch data?
e. g. http://api.xyz.com?CreatedDateFrom=2020-04-01&CreatedDateTo=2020-06-30
Solved! Go to Solution.
Hi @supriya_13 ,
How about create 2 queries in Power Query Editor, and then use relative path, like:
let CreatedDateTo = Date.EndOfMonth(DateTime.LocalNow()),
#"Extracted Date" = Date.From(CreatedDateTo)
in
#"Extracted Date"
let CreatedDateFrom = Date.AddMonths(Date.EndOfMonth(DateTime.LocalNow()),-2),
#"Extracted Date" = Date.From(CreatedDateFrom)
in
#"Extracted Date"
Then, change your data source in Advance Editor like this:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @supriya_13 ,
Try this:
let CreatedDateFrom = Date.AddMonths(Date.EndOfMonth(DateTime.LocalNow()),-2),
#"Extracted Date" = Date.From(CreatedDateFrom),
#"FormatDate" = Date.ToText(#"Extracted Date","yyyy-MM-dd")
in
#"FormatDate"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @supriya_13 ,
Oh, I find the cause:
the problem is that the Power BI service wants to validate the URI before it commits to refreshing the data source. Because the URI isn’t static, there is no URI to validate.
Try something like this:
Web.Contents(
"http://api.xyz.com",
[
Query=
[
CreatedDateFrom=CreatedDateFrom,
CreatedDateTo=CreatedDateTo
]
]
)
Reference:
Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code;
Web.Contents(), M Functions And Dataset Refresh Errors In Power BI.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @supriya_13 ,
How about create 2 queries in Power Query Editor, and then use relative path, like:
let CreatedDateTo = Date.EndOfMonth(DateTime.LocalNow()),
#"Extracted Date" = Date.From(CreatedDateTo)
in
#"Extracted Date"
let CreatedDateFrom = Date.AddMonths(Date.EndOfMonth(DateTime.LocalNow()),-2),
#"Extracted Date" = Date.From(CreatedDateFrom)
in
#"Extracted Date"
Then, change your data source in Advance Editor like this:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey ,
This is really great.
Need 1 more help. I am not able to change the date format in advance query. I want date format ("YYYY-mm-dd") in advance query.
Hi @supriya_13 ,
Try this:
let CreatedDateFrom = Date.AddMonths(Date.EndOfMonth(DateTime.LocalNow()),-2),
#"Extracted Date" = Date.From(CreatedDateFrom),
#"FormatDate" = Date.ToText(#"Extracted Date","yyyy-MM-dd")
in
#"FormatDate"
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @supriya_13 ,
Oh, I find the cause:
the problem is that the Power BI service wants to validate the URI before it commits to refreshing the data source. Because the URI isn’t static, there is no URI to validate.
Try something like this:
Web.Contents(
"http://api.xyz.com",
[
Query=
[
CreatedDateFrom=CreatedDateFrom,
CreatedDateTo=CreatedDateTo
]
]
)
Reference:
Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code;
Web.Contents(), M Functions And Dataset Refresh Errors In Power BI.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @supriya_13 ,
Please refer to these posts:
How to setting a dynamic URL connection;
Dynamic connection to Web URL.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Icey @amitchandak Thank you for reply,Solution is nice but I am looking for settings that will run from back end automatically on month end (like schedular ) and no need to open the report, like what we do in normal coding. Something like that to save time (I have a lot of reports which contains 4-5 data source in single report)
@supriya_13 , Try if this can help
https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |