Data Source Error in Power Query

[basil0713]

Hi, I run few a test which generates 50 text files in a file path. I have created 50 queries to get values from the 50 text files and display in excel column. Sometimes the test  will not able to generate those 50 files. Consequently, I get error dialog box "DataSource.Error: Could not find file..." for all the unavailable text files. Is there any way to handle this error? For example, create M code for this situation where when there is no data source, it just ignores and dont show the error dialog box for all the unavailab

[PhilipTreacy]

Hi @basil0713 

Download sample Excel file

If the source file isn't found then accuracy_final_8 is null, but your code is trying to carry out operations on accuracy_final_8 even if it is null - hence the error message.

Your code is also trying to use a function that doesn't exist Text.RowCount.  It should be Table.RowCount.

In this code I've reorganised it so that any steps that you want to perform after testing if the source file exists are in a small function called DoSteps.  It is easier then to call that function if the file exists, or do nothing if it doesn't

let

    DoSteps = (the_table) =>
    let 
        #"Renamed Columns" = Table.RenameColumns(the_table,{{"Column1", "accuracy_final_8"}}),
        #"Removed Errors" = Table.RemoveRowsWithErrors(#"Renamed Columns", {"accuracy_final_8"})
    in
        #"Removed Errors",

    accuracy_final_8 = 
    let
        Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Filepath],

        Source = Table.FromColumns({Lines.FromBinary(File.Contents(Filepath & "\accuracy_final_8.txt"), null, null, 1252)}),
        //Result = try Text.RowCount(Source)
        Result = try Table.RowCount(Source)
    in
        if Result[HasError] then null else Source,
    
    Final = if accuracy_final_8 = null then null else DoSteps(accuracy_final_8)
in
    Final

I'm also loading the Filepath from a table in Excel which is slightly different to the way you are doing it - but please refer to the sample Excel file linked to above.

I've tested this code with the sample data you screenshotted and it works fine too

Regards

Phil

[basil0713]

---

I'm also loading the Filepath from a table in Excel which is slightly different to the way you are doing it - but please refer to the sample Excel file linked to above.

Is it okay to use my original method to load the filepath or it is better your way?

---

[PhilipTreacy]

Hi @basil0713 

Doesn't matter, I'm just used to using a table to load things like this from Excel.

regards

Phil

[PhilipTreacy]

Hi @basil0713 

OK then you will have to modify all 50 of your queries to catch the error when the Source is missing.  You can do so like this

let
    Source = Csv.Document(File.Contents("C:\Users\PGT\Data.csv"),[Delimiter=",", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Result = try Table.RowCount(Source)
in
    if Result[HasError] then null else Source

Obviously the Source step will be whatever you have, it's the lines after that that you will need to add/modify.

If an error does occur because the source data file is not there, this query will result in null.

You can change it to whatever you like e.g. an empty table.

Regards

Phil

[basil0713]

I get this error after I followed your code.

May i know why this happens? I have tried remove errors too.

[PhilipTreacy]

Hi @basil0713 

Please post the code from your query and some sample data from a file you are trying to load.

Without these it's very difficult to figure out what is going on.

Regards

Phil

[basil0713 · Accepted solution]

Code used:

let
    accuracy_final_8 = let
    Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1], 
    Source = Table.FromColumns({Lines.FromBinary(File.Contents(Filepath & "accuracy_final_8.txt"), null, null, 1252)}),
    Result = try Text.RowCount(Source)
in
    if Result[HasError] then null else Source,
    #"Renamed Columns" = Table.RenameColumns(accuracy_final_8,{{"Column1", "accuracy_final_8"}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Renamed Columns", {"accuracy_final_8"})
in
    #"Removed Errors"

Sample data:

The output in excel for the text files that is available:

[basil0713]

Thank you so much for the help. It works fine!

[PhilipTreacy]

Hi @basil0713 

Use the Folder connector to get all the files : Get Data -> More -> Folder, no need to create a separate query for each file you load.

regards

Phil

[basil0713]

Hi Phil,

I need the data of each text files to be in each excel sheet. For example, text_1.txt in Sheet1, text_2.txt in Sheet2 and so on. Now, I have 50 excel sheets along with their respective connection. Lets say I have 20 text_j.txt file out of 50. I get this data source error pop up for 30x and I have to cancel the error pop up for 30x. How do i resolve this?