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.
Hi,
I have a Power Query which references an OData data source and creates a table within Power BI, but I would like to create a logging table based on the timestamp returned within the dataset:
let
RecordsPerCompanyFct = (CompanyName,TableName) => let
Source = ODataBaseQuery,
SelectCompany = Source{[Name="Company",Signature="table"]}[Data],
FilteredRows = Table.SelectRows(SelectCompany, each ([Name] = CompanyName)),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(FilteredRows, {"Name"}, "Attribute", "Value"),
TargetTable = UnpivotedOtherColumns{[Name=CompanyName,Attribute=TableName]}[Value],
TargetTableWithCompanyName = Table.AddColumn(TargetTable,"Company Name",each CompanyName) // Add "Company Name" column to result
in
TargetTableWithCompanyName,
Custom1 = RecordsPerCompanyFct
in
Custom1
Once the dataset has returned I would like to find the max value in the Timestamp field and insert it into a logging table - Last Record. The logging table should consists of two fields (TableNo Type Int & Max Timestamp Type BigInt).
Any help welcome
Solved! Go to Solution.
Thats basically correct if you Don't run premium. There's another alternative, but I doubt that it will cover your request: https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
So, are you using Premium then and have incremental refresh? Power BI queries generally refresh everything in a table as opposed to inserting it unless you have incremental refresh or are R or Pythonin a query step to call out to an external database.
If you do have a way around it, @ImkeF is probably the fastest way to get an answer.
Exactly that Greg, I am trying to run incremental updates via Odata...Odata v4 supports filtered query, but I need to update based on the current recordset. My Power Query is not my strongest area of Power BI
Yeah, mine neither! That's why I always call in the calvary with @ImkeF! See's a Power Query supra genius!
Hi,
please check this article: https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or...
Meanwhile you can also use Python-script to export if you prefer that: https://www.thebiccountant.com/2018/12/29/export-data-from-power-bi-to-csv-using-python/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@ImkeFthanks for the input
So am I right in thinking that irrespective of whether the odata source can accept a filter that PowerBI always drops the recordset? Therefore incremental builds can only be achieved as you mentioned by moving the data out to CSV first?
Thats basically correct if you Don't run premium. There's another alternative, but I doubt that it will cover your request: https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Well well well...that would explain why it is so difficult to find any reference material on incremental loads then! As you say the Union does look interesting, but not exactly what I am looking for....
Thank alot for all the help
Covering 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 |
---|---|
17 | |
2 | |
2 | |
1 | |
1 |