Reference another Table in the Query Editor (PowerBI or PowerQuery)

Question

I am running a query to update a historical data table within PowerBI. This update query calls the Adobe Analytics API, and pulls data based on a date range I specify.

As I want this to run as fast as possible, I would only like to query the data that I don't already have in my historical data table. I am okay at putting an end date for this query based off Date.Time.LocalNow().

#date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow())-1)

However, where I am stuck is putting a start date. Ideally I would like to take the MaxDate from a table within my data model, and use this value in the query editor as my start date.

How would I do this?

UPDATE 1 - Full code Below

let
Source = AdobeAnalytics.Cubes()
myreportsuiteid = Source{[Id="myreportsuiteid"]}[Data],
#"Added Items" = Cube.Transform(myreportsuiteid,
    {
        {Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
        {Cube.AddAndExpandDimensionColumn, "lasttouchchannel", {"lasttouchchannel"}, {"Last Touch Marketing Channel"}},        
        {Cube.AddMeasureColumn, "Unique Visitors", "uniquevisitors"},
        {Cube.AddMeasureColumn, "Visits", "visits"},
        {Cube.ApplyParameter, "DateRange", {#date(Date.Year(List.Max(Union[Date])), Date.Month(List.Max(Union[Date])), Date.Day(List.Max(Union[Date]))+1), #date(Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow())-1)}},
        {Cube.ApplyParameter, "Segment", {{"s300000554_5ae201be22fa9950dcdbcd92"}}}
    })
in
    #"Added Items"

UPDATE 2

WORKING CODE

Daily US is a table created thorough the Query Editor

let
Source = AdobeAnalytics.Cubes(),
todaysDate = Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),
maxDate = Date.AddDays(List.Max(#"Daily US"[Date]),1),
myreportsuiteid = Source{[Id="myreportsuiteid "]}[Data],

ERROR CODE

Union US is a table created outside of the Query Editor via Union US = DISTINCT(UNION('Seeker US','Daily US'))

let
Source = AdobeAnalytics.Cubes(),
todaysDate = Date.AddDays(DateTime.Date(DateTime.LocalNow()),-1),
maxDate = Date.AddDays(List.Max(#"Union US"[Date]),1),
myreportsuiteid = Source{[Id="myreportsuiteid "]}[Data],

Alexis Olson Alexis Olson · Accepted Answer · 2018-04-30T14:29:17

If the column you want to reference for the MaxDate is named Union[Date], then you should be able to use List.Max to get the maximum value from that column.

Try this:

let
Source = AdobeAnalytics.Cubes(),
MaxDate = List.Max(Union[Date]),
TodaysDate = DateTime.Date(DateTime.LocalNow()),
myreportsuiteid = Source{[Id="myreportsuiteid"]}[Data],
#"Added Items" = Cube.Transform(myreportsuiteid,
    {
        {Cube.AddAndExpandDimensionColumn, "DateGranularity", {"year", "month", "day"}, {"Date Granularity.Level 1: Year", "Date Granularity.Level 2: Month", "Date Granularity.Level 3: Day"}},
        {Cube.AddAndExpandDimensionColumn, "lasttouchchannel", {"lasttouchchannel"}, {"Last Touch Marketing Channel"}},        
        {Cube.AddMeasureColumn, "Unique Visitors", "uniquevisitors"},
        {Cube.AddMeasureColumn, "Visits", "visits"},
        {Cube.ApplyParameter, "DateRange", {MaxDate, TodaysDate}},
        {Cube.ApplyParameter, "Segment", {{"s300000554_5ae201be22fa9950dcdbcd92"}}}
    })
in
    #"Added Items"

Reference another Table in the Query Editor (PowerBI or PowerQuery)

1 Answers