Import multiple files from a folder that can be copied and used for different users

[Giulianna]

I am trying to create a tool for my team where the excel imports files from a folder which contains multiple tamplates and these templates are used to generate different queries, and with the queries working they will upload more files on the folder and that will be "cleaned" and give them the information they need. The problem is that they will use this tool for multiple projects and I would like to find a way to program the tool and queries with a relative path (reference) so they can simply copy and paste this folder with tool and it will be working regarding of the location on their computers. With that being said every time I tried to copy and paste the folder with the tool and tried to add new files to the folder it never worked because the tool only recognizes the absolute path (reference) that the source is my computer. In other words, my coworks would need to upload their files on my computer in the original folder, which is not doable or helpful. If you can help me with that I would really appreciate it. Maybe creating an absoutle path that can reference itself, I am not sure. 

[PwerQueryKees]

Hm. I think that is not related. The error is in the Transfrom Files (5) query and tries to load a PDF. You may want to go there and find the step with the error. It could just be what it says it is: A corrupted file....

[Giulianna]

It worked! I am so happy and very thankful.

Thank you very much, all the best!

[PwerQueryKees · Accepted solution]

If you add one line to CurrentFile it should work:

let
    Source = Excel.CurrentWorkbook(),
    MyFileName = Source{[Name="MyFileName"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(MyFileName,{{"Column1", "Current File Name"}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Current File Name", each Text.BeforeDelimiter(_, "["), type text}}),
    #"Current File Name" = #"Extracted Text Before Delimiter"{0}[Current File Name]
in
    #"Current File Name"

[Giulianna]

Hi, I'm trying to update my query to make it quicker, once with the different samples and helper queries it was getting very heavy. So it is pretty much the same thing I did before, the only difference it's all in only one query I used the same code and followed the same steps you advise me before, but now it is giving me this error:

Do you know what might be causing it?

Here is my query

let
Source = Folder.Files(CurrentDirectory),
#"Added Custom" = Table.AddColumn(Source, "Transform", each Transform([Folder Path]&[Name])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Transform", "Name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Name", "Transform"}),
....

[Giulianna]

It worked for half of the queries, however the other gave me this error:

[PwerQueryKees]

Sorry. I was not clear. I would like to see what data is returned in first, second and last step.

I can already see that your CurrentFule is totally different from mine. Fine, but it does cause your error 😬

I suspect you did not exactly create the worksheet range.

Lets fix this. We work with the way your worksheet has been set up.

[PwerQueryKees]

Sorry, my bad. I did not give you the correct instruction....

[PwerQueryKees]

In your template containing the query, include a cell using the formula =CELL("filename"). Give the cell a range name and you can refer to it in a PowerQuery.

Looking at the description of you problem, you are an advanced user, so I am sure you can take it from here.

If not, share your M code and I can have a look.

[Giulianna]

Hi there, thank you so much for replying. What do you mean give the cell a range name? In this case would I need to add lines with the different names the folder would have when they create new project and hence copying the original one and pasting to create a new one related to the new project? If so that would not be ideal, because it is really hard to keep up with all the projects, there are too many of them. Below is my M code:

let
Source = Folder.Files("C:\Users\116366\OneDrive - Sidel\Power query - Labor calculation\Test - template"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (4)", each #"Transform File (4)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample GTPA"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"", type text}, {"Date", type text}, {"Column3", type text}, {"Total Work#(lf)(In hours)", type text}, {"Including#(lf)Night#(lf)(In hours)", type text}, {"Travel#(lf)(In hours)", type text}, {"Column7", type text}, {"am", type text}, {"pm", type text}, {"A", type text}, {"B", type text}, {"C", type text}, {"Column13", type text}, {"D", type text}, {"E", type text}, {"F", type text}, {"G", type text}, {"H", Int64.Type}, {"I", type text}, {"J", Int64.Type}, {"K", type text}, {"Column22", type text}, {"Column23", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column3"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"I", "Labor time"}, {"K", "Travel time"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"J", "Column22", "Column23", "H", "G", "E", "F", "D", "Column13", "C", "A", "B", "am", "Travel#(lf)(In hours)", "Column7", "Total Work#(lf)(In hours)", "pm"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Employee", each "GTPA"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Source.Name", "Employee", "", "Date", "Including#(lf)Night#(lf)(In hours)", "Labor time", "Travel time"}),
#"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each [Date] <> null and [Date] <> ""),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows1",{"Including#(lf)Night#(lf)(In hours)"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"", "Day of week"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns2", "Labor", each if ([Labor time]<>null or [Travel time]<>null) then if [Labor time]=null then 0 else [Labor time] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Travel", each if ([Labor time]<>null or [Travel time]<>null) then if [Travel time]=null then 0 else [Travel time] else null),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom2",{"Labor time", "Travel time"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns3", each [Labor] <> null and [Labor] <> ""),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows", each [Date] <> "Date"),
#"Removed Columns4" = Table.RemoveColumns(#"Filtered Rows2",{"Source.Name", "Employee"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns4",{{"Travel", type number}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "Travel time", each [Travel]/10),
#"Added Custom11" = Table.AddColumn(#"Added Custom3", "Day ", each if[Day of week]="Thusday" then "Tuesday" else [Day of week]),
#"Removed Columns8" = Table.RemoveColumns(#"Added Custom11",{"Day of week"}),
#"Removed Columns5" = Table.RemoveColumns(#"Removed Columns8",{"Travel"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns5",{{"Day ", "Day of week"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Labor", type number}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type2", "Labor time", each [Labor]/10),
#"Removed Columns6" = Table.RemoveColumns(#"Added Custom4",{"Labor"}),
#"Added Custom5" = Table.AddColumn(#"Removed Columns6", "Travel time weekend", each if[Travel time]>0 and ([Day of week]="Saturday" or [Day of week]="Sunday") then [Travel time] else null),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Travel weekday", each if [Travel time]>0 and ([Day of week]="Monday" or [Day of week]="Tuesday" or [Day of week]="Wednesday" or [Day of week]="Thursday" or [Day of week]="Friday") then [Travel time]else null),
#"Removed Columns7" = Table.RemoveColumns(#"Added Custom6",{"Travel time"}),
#"Added Custom7" = Table.AddColumn(#"Removed Columns7", "Regular time", each if [Labor time]>0 and ([Day of week]="Monday" or Text.Contains([Day of week],"Tuesday") or [Day of week]="Wednesday" or [Day of week]="Thursday" or [Day of week]="Friday") then if [Labor time]>8 then 8 else [Labor time] else null),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "OT", each if [Labor time]>0 and ([Day of week]="Monday" or [Day of week]="Tuesday" or [Day of week]="Wednesday" or [Day of week]="Thursday" or [Day of week]="Friday") then if [Labor time]>8 then [Labor time]-8 else null else null),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Saturday rate", each if [Labor time]>0 and [Day of week]="Saturday" then [Labor time] else null),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Sunday rate", each if [Labor time]>0 and [Day of week]="Sunday" then [Labor time] else null),
#"Removed Columns9" = Table.RemoveColumns(#"Added Custom10",{"Labor time"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns9",{"Date", "Day of week", "Travel weekday", "Travel time weekend", "Regular time", "OT", "Saturday rate", "Sunday rate"}),
#"Filtered Rows3" = Table.SelectRows(#"Reordered Columns1", each [Day of week] <> "Ex")
in
#"Filtered Rows3"

[PwerQueryKees]

Your query starts with the line 

Folder.Files("C:\Users\116366\OneDrive - Sidel\Power query - Labor calculation\Test - template")

We are going to change that into

Folder.Files(CurrentDirectory)

But before we do that, we need to creae a query "CurrentDirectory" as follows

In the workbook where you have your query, add a text and a formula as follows:

Then make a range "MyFileName" pointing to the full name of your file as follows:

Now open PowerQuery (Press Alt-F12), and create a new query and name it CurrentDirectory:

Here is the query you can CopyPaste:

let
    Source = Excel.CurrentWorkbook(),
    MyFileName = Source{[Name="MyFileName"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(MyFileName,{{"Column1", "Current File Name"}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Current File Name", each Text.BeforeDelimiter(_, "["), type text}})
in
    #"Extracted Text Before Delimiter"

 Now go to your original query and replace the first line:

Folder.Files(CurrentDirectory)

And from now on, the files you process will be taken from the same directory as the excel you placed you query in....

If you do not store your query excel in the same directory you need to manipulate CurrentDirectory to suit the setup you want to achieve.

Hope this helps!

[Giulianna]

Thank you so much! The process you proposed makes a lot of sense to me. I followed all the step, but now after I went to myoriginal query and replaced the first line:

Folder.Files(CurrentDirectory) 

it is now giving me this error:

Please, let me know if I made a mistake on the way or what is the problem. Thank you.

[PwerQueryKees]

Hard to see. Press go to error and share a screenprint in cluding the data,..

[Giulianna]

Sorry, here it is. I don't know if the problem is I have a lot of queries and helper queries too with samples, parameters and functions:

[PwerQueryKees]

And what does CurrentDirectory look like? Open the query. I would like to see first and last step data.

[Giulianna]

let
Source = Excel.CurrentWorkbook(),
MyFileName = Source{[Name="MyFileName"]}[Content],
#"Renamed Columns" = Table.RenameColumns(MyFileName,{{"Column1", "Current File Name"}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Renamed Columns", {{"Current File Name", each Text.BeforeDelimiter(_, "["), type text}})
in
#"Extracted Text Before Delimiter"

[Omid_Motamedise]

Instead of using local files, you can use google drive and then load data from it like the below link

https://www.cdata.com/kb/tech/googledrive-connect-power-query.rst

[Giulianna]

If I use a sharepoint document library wouldn't they have the same problem when copying and pasting the folder (for different projects) on the sharepoint where they would have to go to the original folder to add the new files?

[lbendlin]

Use a network folder or SharePoint Document Library.

Note:  You cannot dynamically generate new queries. All queries must pre-exist.  (You can change the query outcomes).