Missing file while Getting data from folder

[amirmosh]

I wrote a query that gets a table of file names in order to append the files data into one table.
The files are located in a specific folder.
Not always the folder contains the file name.
While using the get file from folder I receive an error in the "source" step.
Is there an efficient way to deal
With this error?

Thanks

 

[pinarello]

Create a dummy workbook that contains only the header row of the table to be imported. The "Source" step is then included in "try/othwerwise". This is how it works for me in any case. There may be other possibilities, but I am not aware of them.

let
    Source          = try        Excel.Workbook(File.Contents("D:\xlguru-data\Duration Test - not always there.xlsx"), null, true)
                      otherwise  Excel.Workbook(File.Contents("D:\xlguru-data\Duration Test - when original is not there.xlsx"), null, true),
    Import_from_wb  = Source{[Item="Import_from_workbook",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Import_from_workbook_Table,{{"Name", type text}, {"Time", type number}, {"Date", type number}, {"Number", type number}, {"Time as number", type number}, {"Date as Number", type number}, {"Duration", type datetime}})
in
    #"Changed Type"

 

[amirmosh]

Create a dummy workbook that contains only the header row of the table to be imported. The "Source" step is then included in "try/othwerwise". This is how it works for me in any case. There may be other possibilities, but I am not aware of them.

let
    Source          = try        Excel.Workbook(File.Contents("D:\xlguru-data\Duration Test - not always there.xlsx"), null, true)
                      otherwise  Excel.Workbook(File.Contents("D:\xlguru-data\Duration Test - when original is not there.xlsx"), null, true),
    Import_from_wb  = Source{[Item="Import_from_workbook",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Import_from_workbook_Table,{{"Name", type text}, {"Time", type number}, {"Date", type number}, {"Number", type number}, {"Time as number", type number}, {"Date as Number", type number}, {"Duration", type datetime}})
in
    #"Changed Type"

Hi,
Thanks for your input
Can it be that your suggestion does not work while using it
On a folder?
I mean
Source = try Folder.Files("X:\...")
otherwise ....
Thanks,

 

[pinarello]

Then I must have misunderstood your problem. Maybe it would help if you show your query code and use it to explain the problem again.

 

[amirmosh]

Create a dummy workbook that contains only the header row of the table to be imported. The "Source" step is then included in "try/othwerwise". This is how it works for me in any case. There may be other possibilities, but I am not aware of them.

let
    Source          = try        Excel.Workbook(File.Contents("D:\xlguru-data\Duration Test - not always there.xlsx"), null, true)
                      otherwise  Excel.Workbook(File.Contents("D:\xlguru-data\Duration Test - when original is not there.xlsx"), null, true),
    Import_from_wb  = Source{[Item="Import_from_workbook",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Import_from_workbook_Table,{{"Name", type text}, {"Time", type number}, {"Date", type number}, {"Number", type number}, {"Time as number", type number}, {"Date as Number", type number}, {"Duration", type datetime}})
in
    #"Changed Type"

Hi,
Thanks for your input
Can it be that your suggestion does not work while using it
On a folder?
I mean
Source = try Folder.Files("X:\...")
otherwise ....
Thanks,

 

[pinarello]

Yes, with "Folder.Files" it doesn't work with try/otherwise for me either. So at the moment I see only the possibility to control the call of the query by a macro, in which it is checked whether the relevant folder is currently available.

 

[amirmosh]

Yes, with "Folder.Files" it doesn't work with try/otherwise for me either. So at the moment I see only the possibility to control the call of the query by a macro, in which it is checked whether the relevant folder is currently available.

Its good to know that I'm not the only one that it doesn't work for...
By Marco you mean a vba macro?
I have used yet a macro for calling a power query...
Does this macro runs parallel to the power query or performs this check before running the query?

 

[pinarello]

For instance:

Sub Test_Folder_Exist_With_Dir()
'Updateby Extendoffice
    Dim sFolderPath As String
    sFolderPath = "C:\Users\MyUserName\Desktop\"
    
    If Dir(sFolderPath, vbDirectory) <> vbNullString Then
        ActiveWorkbook.Connections("Your Query Name").Refresh
    Else
        MsgBox "Folder doesn't exist", vbInformation, "Kutools for Excel"
    End If
End Sub

 

[amirmosh]

For instance:

Sub Test_Folder_Exist_With_Dir()
'Updateby Extendoffice
    Dim sFolderPath As String
    sFolderPath = "C:\Users\MyUserName\Desktop\"
   
    If Dir(sFolderPath, vbDirectory) <> vbNullString Then
        ActiveWorkbook.Connections("Your Query Name").Refresh
    Else
        MsgBox "Folder doesn't exist", vbInformation, "Kutools for Excel"
    End If
End Sub

Hi,
Thanks for you help.
I understand your answer & it's great.
Anyway,I would like to check if there is still a solution in the boundaries of power query...
Do you know someone here that can help with this or perhaps recommend a reading reference?

 

[pinarello]

So, I now have a working PQ solution entirely without VBA.

The directory to import from is specified in the named field "wk_dir" and a dummy directory, in case the directory specified in wk_dir does not exist, is specified in the named field "wk_dummy_dir".

The steps of the PQ are as follows:

Import wk_dir and wk_dummy_dir
wk_dir to Table
Determine the number of directory levels
column for the directory above
Drill down for the directory above
Import the directory above
Filter in "Folder Path" column with wk_dir
Delete all other columns
Drill down to "Folder Path" with try/otherwise
With if statement Folder.Files for wk_dir/wk_dummy_dir

 

[amirmosh]

So, I now have a working PQ solution entirely without VBA.

The directory to import from is specified in the named field "wk_dir" and a dummy directory, in case the directory specified in wk_dir does not exist, is specified in the named field "wk_dummy_dir".

The steps of the PQ are as follows:

Import wk_dir and wk_dummy_dir
wk_dir to Table
Determine the number of directory levels
column for the directory above
Drill down for the directory above
Import the directory above
Filter in "Folder Path" column with wk_dir
Delete all other columns
Drill down to "Folder Path" with try/otherwise
With if statement Folder.Files for wk_dir/wk_dummy_dir

Hi,

I followed your solution ...
If I understood correctly then your solution goes over a list of folders & if a folder/directory is missing than it returns "not there".
I was looking for a solution in the kind of "on the fly", meaning that there is no "pre-check" for a missing folder.
During the run, if there is a missing folder then the try - otherwise , deals with it.

I would like to share with you somthing I found in the web-site:"myonlinetraininghub" which helped me with a solution,
it is the "table.buffer" option/command that I write: Source =Table.Buffer (try Folder.Files("X:\..."))
I dont know what it does excatly but it looks like it works.

Amir

 

[pinarello]

Hello,

it doesn't work for me. but i can't check what the reason is, because you didn't give the complete reference.

If I understood correctly then your solution goes over a list of folders & if a folder/directory is missing than it returns "not there".

No, this is not quite correct. Exactly only from the directory from which you want to import the data, it reads the layer above to determine if the desired directory exists.

If it exists, then it will be imported, otherwise from an empty dummy directory.