Power Query | Loop with delay

0
votes

I'm new to PQ and trying to do following:

  1. Get updates from server
  2. Transform it.
  3. Post data back.

While code works just fine i'd like it to be performed each N minutes until application closure. Also LastMessageId variable should be revaluated after each call of GetUpdates() and i need to somehow call GetUpdates() again with it.

I've tried Function.InvokeAfter but didn't get how to run it more than once. Recursion blow stack out ofc.

The only solution i see is to use List.Generate but struggle to understand how it can be used with delay.

let
    //Get list of records
    GetUpdates = (optional offset as number) as list => 1,
    Updates = GetUpdates(),
    // Store last update_id
    LastMessageId = List.Last(Updates)[update_id],
    // Prepare and response
    Process = (item as record) as record =>
    // Map Process function to each item in the list of records
    Map = List.Transform(Updates, each Process(_))
in
    Map
1
powerbipowerquerym
See Value.WaitFor() for a loop with a delay, like async polling docs.microsoft.com/en-us/power-query/… – ninMonkey

1 Answers

0
votes

PowerBI does not support continuous automatic re-loading of data in the desktop.

Online, you can enforce a refresh as fast as 15 minutes using direct query1

Alternative methods:

  • You could do this in Excel and use VBA to re-execute the query on a schedule
  • Streaming data in PowerBI2
  • Streaming data with Flow and PowerBI3

1: Supported DirectQuery Sources

2: Realtime Streaming in PowerBI

3: Streaming data with Flow

4: Don't forget to enable historic logging!