Forum Discussion

TimAdams's avatar
TimAdams
Frequent Visitor
7 years ago
Solved

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

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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.

    • TimAdams's avatar
      TimAdams
      Frequent 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_Deckler's avatar
        Greg_Deckler
        Community Champion

        Yeah, mine neither! That's why I always call in the calvary with ImkeF! See's a Power Query supra genius!