Forum Discussion
Use formulas in Power Query Parameters
Hi all,
How would I create parameters which stores "YYYYMM"-value based on a date input (from another parameter).
I would like to use incremental refresh on a dataset. However, this dataset does not contain a date column, it only has a "YYYYMM" Column. I think I found a workaround.
I can use the RangeStart and RangeEnd parameters for the incremental refresh, which is determined by the settings in Power BI service. I would use two extra parameters which convert RangeStart and RangeEnd into a "YYYYMM"-format which in turn I can use for filtering the datasets; MonthYearRangeStart and MonthYearRangeEnd.
How would I create these 'dynamic' parameters?
Turning the original YYYYMM column into a date format is not an option, because then I would lose the Query folding.
I think I've found a way to achieve this:
Create a reference for the Parameter. Edit this new reference, for example, for the RangeStart Parameter rename the reference name to YYYYMM-RangeStart and the contents to:
= Number.FromText(Number.ToText(Date.Year(RangeStart)) & Number.ToText(Date.Month(RangeStart)))Create a filter for the YYYYMM-column:
each [#"Calendar year/month"] >= #"YYYYMM-RangeStart" and [#"Calendar year/month"] <= #"YYYYMM-RangeEnd"
1 Reply
I think I've found a way to achieve this:
Create a reference for the Parameter. Edit this new reference, for example, for the RangeStart Parameter rename the reference name to YYYYMM-RangeStart and the contents to:
= Number.FromText(Number.ToText(Date.Year(RangeStart)) & Number.ToText(Date.Month(RangeStart)))Create a filter for the YYYYMM-column:
each [#"Calendar year/month"] >= #"YYYYMM-RangeStart" and [#"Calendar year/month"] <= #"YYYYMM-RangeEnd"