How to import files from different paths

[skurek]

Hi.

How to import files from different locations?

I have paths to files saved in Excel table.

I can import one path from Excel to PQ:

= Folder.Files(paths{0}[Column1])

but this method gets only one path to files.

how to write own function (?) to get in loop all folders to files?

[skurek]

Hi.

How to import files from different locations?

I have paths to files saved in Excel table.

I can import one path from Excel to PQ:

= Folder.Files(paths{0}[Column1])

but this method gets only one path to files.

how to write own function (?) to get in loop all folders to files?

[Greg_Deckler]

You could create a separate query for each file, or simply create multiple "Source" lines. Either way, just append all of them together in the end. Not sure I can be more specific. @ImkeF might have some thoughts as well.

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\jobid.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source1 = Csv.Document(File.Contents("C:\temp\powerbi\months.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"JobID", Int64.Type}, {"Date", type date}}),
    #"Appended Query" = Table.Combine({Source, Source1})
in
    #"Appended Query"

[skurek]

file list is dynamical and i dont know how many files is in the table and i dont know files names...

so i cant write separate query for each file...

[ImkeF]

I don't understand your request:

If you import from a folder, all files in that folder and all files in all its subfolders will be imported. UNLESS: You filter some files in them. So you just have to grab the uppermost path to grab all files in it.

If you have any other kind of logic to identify what shall be imported, please try to explain better.

[skurek]

I have files in many shared locations:

1. server in Poland, many folders and files

2. server in US, many folders and files...

3. ...

I can import all folders and files from these shared locations. BUT this cost PQ a lot of time.

Files and folders in these locations is too many.

before these files are displayed in PQ and I will filter them in pq - it takes a long, long time...

so i need to import only concrete files from shared locations...

[ImkeF]

Sorry, I didn't read your original post thoroughly enough.

If you have a (dynamic) list with multiple filenames, don't use the Folder.Files-function, but instead the Excel.Workbook-function:

1) Convert your list with filepaths to a table 

2) Add a custom column to that table with the following code:

Excel.Workbook(File.Contents([ColumnNameContainingPaths])){[Item="NameOfTheTables"]}[Data]

You have to adjust the bolded parts and then just expand that new column.

[KWilkinson02]

Hi @ImkeF would you be able to provide any more detail on this solution, I'm struggling to follow it with my basic knowledge of Power Query... 

I can create a table called Filepaths with a column header called Filepaths but I'm not sure where I create the custom column or if the ColumnNameContainingPaths would be Filepaths or something else.  Any assistance gratefully received.

Cheers

K.

[ImkeF]

Hi @KWilkinson02 ,

please start with importing just 1 file via the "From Excel-Method". Then check the M-code that has been generated. In the following steps you will replace the path to that file with the reference to a column. So the way that path has been written needs to match one of the columns from your solution.

Please check out this blogpost, which describes the general method (although it is for multiple webpages, the general princple is the same): https://www.thebiccountant.com/2018/02/10/web-scraping-1-combining-multiple-tables-from-one-page-in-... 

[KWilkinson02]

Sorry @ImkeF still struggling.  I can see how to add in the custom column and create the named table okay but the formula in your previous posts keeps telling me the field of the record wasn't found... If you have time to help a little further I would be very grateful.  This is what I have;

My table named 'Filepath' (I'm hoping these images expand!!) which contains my file paths as a single column called Column1

I've then created a custom column as per your code suggestion replacing column name with 'Column1' and table name with 'Filepath' however I just get an error telling me Column1 doesn't exist.

I'm also not clear how I then use that to actually grab the files... 

Sorry if I'm being a bit thick here, this is the only post I can find that even remotely provides a solution to what I'm trying to achieve... 

[KWilkinson02]

After a bit more fiddling with this, I think I see what I'm trying to do at least.  

The Source = Excel.Workbook(FileContents ... line needs to point to the list of file paths that are defined in my table.  What I'm not understanding is why I need to create a custom column and why that doesn't work... 

I'm definitely trying to run before I can walk here but I really need to find a solution to this now or it will haunt me!

[KWilkinson02]

Thanks a lot @ImkeF , I will work through that now.

[skurek]

thank you!

[v-piga-msft]

Hi @skurek,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[ImkeF]

You can start from a table with one pathname in each row. Then you have to convert your From-Path-query into a function and apply it in a custom column. Just expand the columsn afterwards.