Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
So I'm trying to add a relative date range in Power Query Editor.
I've connected to my Adobe Analytics account and can pull some data through, however i can only hard code a date range. I'm hoping to have a relative date range in the code to pull all data on or after 01 April 18.
Here's what i have so far with the line of code i need to change:
= Cube.Transform(qantascom,
{
{Cube.ApplyParameter, "DateRange", {#date(2018, 1, 1), #date(2018, 11, 26)}},
{Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
{Cube.ApplyParameter, "Top", {1000, "page"}},
{Cube.AddAndExpandDimensionColumn, "page", {"page"}, {"Page"}},
{Cube.AddAndExpandDimensionColumn, "evar60", {"evar60"}, {"Page Title"}},
{Cube.AddAndExpandDimensionColumn, "prop7", {"prop7"}, {"Page Title.1"}},
{Cube.AddMeasureColumn, "Unique Visitors", "uniquevisitors"},
{Cube.AddMeasureColumn, "Visits", "visits"},
{Cube.ApplyParameter, "Segment", {{"s200001517_5b74b9fb506c8f23b82f080d"}}}})
Any Ideas?
Solved! Go to Solution.
HI @Anonymous,
I'd like to suggest you modify your formula to parametrized query with query parameters:
Deep Dive into Query Parameters and Power BI Templates
For example: define two parameters with date type and use them to replace your query formula
{Cube.ApplyParameter, "DateRange", {startDate,endDate}},
In addition, you can also consider to use Datetime.Localnow to use local datetime as filter parameter.
Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month
Regards,
Xiaoxin Sheng
HI @Anonymous,
I'd like to suggest you modify your formula to parametrized query with query parameters:
Deep Dive into Query Parameters and Power BI Templates
For example: define two parameters with date type and use them to replace your query formula
{Cube.ApplyParameter, "DateRange", {startDate,endDate}},
In addition, you can also consider to use Datetime.Localnow to use local datetime as filter parameter.
Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month
Regards,
Xiaoxin Sheng
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |