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.
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?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi,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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |