Forum Discussion
Find Max Value After Odata Query & Insert
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
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/
7 Replies
- Greg_DecklerCommunity Champion
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.
- TimAdamsFrequent Visitor
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
- Greg_DecklerCommunity Champion
Yeah, mine neither! That's why I always call in the calvary with ImkeF! See's a Power Query supra genius!