As a postscript to my series on Power BI refresh timeouts (see part 1, part 2 and part 3) I thought it would be useful to document how I was able to simulate a slow data source in Power BI without using large data volumes or deliberately complex M code.
It’s relatively easy to create an M query that returns a table of data after a given delay. For example, this query returns a table with one column and one row after one hour and ten minutes:
let Source = Function.InvokeAfter( () => #table({"A"}, {{1}}), #duration(0, 1, 10, 0) ) in Source
Some notes:
- I’m using #table to return the table without having to connect to a data source. More details on how to use #table can be found here.
- The delay is achieved using the Function.InvokeAfter M function, with the amount of time to wait for specified using #duration
A more interesting problem is how to create an M query that, instead of waiting for a given duration and then returning a table immediately, returns the rows of a table one at a time with a delay between each row. Here’s a query that does that, returning ten rows one second at a time:
let NumberOfRows = 10, DaysToWait = 0, HoursToWait = 0, MinutesToWait = 0, SecondsToWait = 1, Source = #table( {"A"}, List.Transform( {1 .. NumberOfRows}, each Function.InvokeAfter( () => {1}, #duration( DaysToWait, HoursToWait, MinutesToWait, SecondsToWait ) ) ) ) in Source
Last of all, to simulate a slow SQL Server data source – not being much good at TSQL at all – I borrowed some code from this thread on Stack Overflow to create a function that returns a scalar value after a specified number of seconds:
CREATE FUNCTION [dbo].[ForceDelay](@seconds int) returns int as BEGIN DECLARE @endTime datetime2(0) = DATEADD(SECOND, @seconds, GETDATE()); WHILE (GETDATE() < @endTime ) SET @endTime = @endTime; return 1; END
I showed how to call this function from Power Query using a native SQL query here.
@Chris – Don’t need to do anything to emulate “slow” in Power Query – It slooooooooooow by default – thanks to M’s streaming semantics – So Just rename a step and watch the wheel spin 😉
Hoping to get a manual mode during query edits in the coming year
Advance New year and Xmas wishes to you – thanks for all the great posts during the year
Cheers
Sam