How to query day old data ?

[Anonymous]

Hello, I have a case where I am querying 2 databases. One of which is day old data (lets call this DB1), due to the ETL process, then the other database is live data, (lets call this DB2).

I would like to line up my queries to have DB2 be a day old, then take the live data from DB1 ( which is already a day old because of ETL).

Is this possible using Power BI? Do I need to key off a timestamp in my live database?

[edhans]

Yes, this is possible, and you can even have this folded to your server to do all of the work if it is a supported relational database, like SQL Server.

If you have a time stamp in your system, you just need to create a variable in Power Query. For example, our system uses YYYYMMDD for dates (integers) vs a true date field. I needed to create a query that only pulled the most recent year. I called this "varOneYearAgo" as the query, and it returns an integer.

let
    Source = DateTime.LocalNow(),
    #"Current Date" = DateTime.Date(Source),
    #"One Year Ago" = Date.AddYears(#"Current Date",-1),
    #"Final Date" = Date.Year(#"One Year Ago") * 10000 + Date.Month(#"One Year Ago") * 100 + Date.Day(#"One Year Ago")
in
    #"Final Date"

This returns 20181025 today. Just change your function to use Date.AddDays to go back one day, then your #"Final Date" statement would need to be whatever it took to get a date in the same format as your system.

Then as an example, this is the M code to pull a table and only return order numbers less than 1 year old:

let
    Source = Sql.Database("Server", "Database"),
    dbo_TableName= Source{[Schema="dbo",Item="dbo_TableName"]}[Database],
    #"Removed Other Columns" = Table.SelectColumns(dbo_TableName,{"ord_no", "ord_dt"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([ord_dt] >= varOneYearAgo))
in
    #"Filtered Rows"

The SQL this generates and sends to the server is:

select [_].[ord_no],
    [_].[ord_dt]
from 
(
    select [ord_no],
        [ord_dt]
    from [dbo].[TableName] as [$Table]
) as [_]
where [_].[ord_dt] >= 20181025

Notice it relaced the variable with the integer that varOneYearAgo returned.

[YJ]

Hi,m

i chanced upon this and would be interested to know how this is done to fold the query back to SQL side.

I am still learning esp on the M language,

can you explain a bit more the below: 

 dbo_TableName= Source{[Schema="dbo",Item="dbo_TableName"]}[Data],

What is `[data ] here, and 

[$Table]

?

Thanks

[edhans]

Data should say "Database". I forgot to change that when I was removing my specific names.

I have to be honest. I am not sure specifically what [Data] is doing other than telling the server to return data in the specified table. I cannot find the syntax for the Source command.

$Table is the variable I guess Power Query generates. I didn't create any of the SQL statement. Just showing what PQ generates.