cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jeffbruin
New Member

Handling Power query refresh errors resulting from source file refresh

Good Day,

 

I am using power query to automatically pull data from 3 source files every 5 minutes.  These source files automatically update at different frequencies from our ERP system . When the power query performs an auto update the same time the source file is in the process of being updated, I get the following error:

Jeffbruin_0-1669650363498.png

 

Upon acknowledgement and subsequent refresh, it works as desired. 

 

It would be greatly appreciated if I can get help - an ideal solution would automatically close the warning prompt and display the same information until power query performs the next auto update. Any straight forward VBA code can help with this? 

 

Much appreciated. 

 

 

 

4 REPLIES 4
Jeffbruin
New Member

Thanks for the reply. Believe through Phython it pulls the data to a local network folder from the ERP and saves over the previous file. Was hoping an error handling VBA would be able to acknowdlege and subsequently ignore the errors until the next power query refresh iteration. 

 

The issue with this is the way in which Power Query works. It wipes everything before it starts attempting the new import and transformation process. Therefore, as soon as you start, you have generally to complete or you get a failure with no original data retention.

If the refresh frequency weren't so high I'd suggest putting these into Dataflows, which would actually retain your original data upon failure, but then you're chaining two refresh processes into a narrow five-minute window each time, so risks refresh overlap. If these are very small files and refresh in a matter of seconds, this would be the way I would go, using Power Automate to detect the completion of a Dataflow refresh and automatically initiate the dataset refresh.

Beyond this, I think you'd want to explore what @jbwtp has suggested, by looping attempts within the query until you get a successful response.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi @Jeffbruin,

 

I believe that the problem that you have is because the PQ tries to fetch data while the file is being re-written. Do you mind trying to apply to your code the following pattern:

let
    fetch_data = (MaxAttempts, DelayBetweenAttempts) =>
        let
            Numbers = List.Numbers(1, MaxAttempts),
            FileSystemCalls = List.Transform(Numbers, each try Function.InvokeAfter(()=>internal_fetch_data(), if _ > 1 then DelayBetweenAttempts else #duration(0,0,0,0)) otherwise null),
            OnlySuccessful = List.Select(FileSystemCalls, each _ <> null),
            Result = List.First(OnlySuccessful, null)
            in
        Result,


    internal_fetch_data = () =>
        let 
            // this is your origianl query from the first line to the step that throughs out the error (inclusive on both sides)
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0tSk9NUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merged = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Merged", type text}})
        in #"Changed Type", 
    
    #"Uppercased Text" = Table.TransformColumns(fetch_data(10, #duration(0,0,0,10) /*10 sec delay*/),{{"Merged", Text.Upper, type text}})
in
    #"Uppercased Text"

 

This tries to get the data from the file several times each time with some delay. Maybe this will be sufficient in your case and resolve the problem? Obvoiusly, you will nbeed to adjust it to your case by altering the content of the internal_fetch_data function.

 

Thanks,

John 

BA_Pete
Super User
Super User

Hi @Jeffbruin ,

 

Where does your ERP save the source files to? Would it be possible to use Power Automate to detect a change in file modification date then copy the file somewhere else to be used for Power Query?

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors