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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

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!


I have book! Learn Power BI from Packt


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




Highlighted
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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 308 members 3,008 guests
Please welcome our newest community members: