Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TimAdams
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

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

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.