Store file name as column value on power query

[Dee]

I'm importing data from different sources, excel and csv, I'm looking for a way to add a column that specifies the name of the source file.

eg. DataSource 1 of type excel has the field animal name and the name of the file is Test 1.xlsx, DataSource 2 has the same format but of type csv.

Desired output.

Name  SourceName

Cat       Test1.xlsx

Dog      Test1.xlsx

Cat        Test.csv

I would like this to be updated on each import.

Any help on how to achieve this would be highly appreciated

[PhilipTreacy]

@Dee 

Ah. that wasn't mentioned in your initial question.

[PhilipTreacy]

HI @Dee 

If you use load the file(s) using Get Data -> From Folder, then filter for the file(s) you want, you'll end up with a column showing the filename, for example

let
    Source = Folder.Files("d:\temp"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "data.xlsx"))
in
    #"Filtered Rows"

Gives me this

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Dee]

Hi @PhilipTreacy 

I am getting data from a web source.

Web API specifically on powerapps power query.

[mahoneypat]

This is possible.  You start with your table of filenames and folderpaths, then you can concatenate those into into a custom column, either using a function or an if then else that use Excel.Workbook or CSV.Document depending on file type.  It would be easier to have one query that gets all your CSVs (potentially two separate queries, one for each Folder), and another that gets all your Excel files (or two).  You can then make sure the column names match and append them all together.  You can then uncheck  Enable Load on all but the final appended table.

Regards,

Pat