cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimAdams Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Find Max Value After Odata Query & Insert

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/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




7 REPLIES 7
Super User
Super User

Re: Find Max Value After Odata Query & Insert

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
TimAdams Frequent Visitor
Frequent Visitor

Re: Find Max Value After Odata Query & Insert

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

Super User
Super User

Re: Find Max Value After Odata Query & Insert

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Find Max Value After Odata Query & Insert

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/ 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




TimAdams Frequent Visitor
Frequent Visitor

Re: Find Max Value After Odata Query & Insert

@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?

Super User
Super User

Re: Find Max Value After Odata Query & Insert

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/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




TimAdams Frequent Visitor
Frequent Visitor

Re: Find Max Value After Odata Query & Insert

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