Import Data from a List of Files

[Anonymous]

Hi,

I have seen the post below and I'm trying to achieve something similar but I am struggling to get Power Query to work, so I was hoping I could reach out for some help - (PQ is new to me!).

https://community.powerbi.com/t5/Desktop/How-to-import-files-from-different-paths/td-p/594377

I currently have a list of excel circa 20 filepaths in a table in my workbook.

(e.g. M:\Risks\Programme Level Template\Example Programme\Project Risk Register - Locked - V6 - Copy (1).xlsm)

The table name is "FilePath" and the column name is "Filepath". 

In my previous attempt, I got all of the data to load by creating 20 appended queries, one for each file path using named cells but I ran into errors if there were blank cells. I used the following function:

let GetProject=(NamedRange) =>
let
name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
value = name{0}[Column1]
in
value

in GetProject

and 20 of the following queries where the "Project1FilePath" was incremented from 1 -20 for each file and they were appended so they loaded in sequence:

let
    Source = Excel.Workbook(File.Contents(functionGetProject("Project1FilePath")), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Project ID", Int64.Type}, {"Risk ID", Int64.Type}, {"Date Raised", type date}, {"Risk Theme", type any}, {"Risk Name", type any}, {"Risk Details", type text}, {"Risk Owner", type any}, {"Risk Open / Closed", type any}, {"Risk Levels#(lf)R1 = Project#(lf)R2 = Programme", type text}, {"Generic Risk / Project Specific Risk ", type any}, {"Opp / Threat#(lf)(based on Impact score)", type text}, {"Risk Impact Category", type text}, {" Risk Impact Rating", Int64.Type}, {"Risk Category Details", type text}, {"Probability", Int64.Type}, {"Probability Details", type number}, {"Priority", type text}, {"Matrix Rating", Int64.Type}, {"Cost Impact", Int64.Type}, {"Cost Impact Detail", type text}, {"Schedule Impact", Int64.Type}, {"Schedule Impact Detail", type text}, {"Planned #(lf)Risk Response Summary#(lf)Risk Controls", type any}, {"Response Type", type any}, {"Actions", type text}, {"Number of Open Actions", Int64.Type}, {"Post Mitigation Risk Impact Category", type any}, {"Post Mitigation Risk Impact Rating", type any}, {"Risk Category Details2", Int64.Type}, {"Post Mitigation Probability", type any}, {"Post Mitigation Probability Details", type any}, {"Post Mitigation Priority", type any}, {"Post Mitigation Matrix Rating", Int64.Type}, {"Post Mitigation Cost Impact", type any}, {"Post Mitigation Cost Impact Detail", type any}, {"Post Mitigation Schedule Impact", type any}, {"Post Mitigation Schedule Impact Detail", type any}, {"P6 Activities Impacted - WBS ID", type any}, {"Last Risk Updated", type any}, {"Risk Closed Date", type any}, {"Cost Monte Carlo Probability", type any}, {"Min Cost Impact", type any}, {"Most Likely Cost Impact", type any}, {"Max Cost Impact", type any}, {"Cost PERT", type any}, {"Risk Cost Exposure", type any}, {"Schedule Monte Carlo Probability", type any}, {"Min Schedule Impact", type any}, {"Most Likely Schedule Impact", type any}, {"Max Schedule Impact", type any}, {"Schedule PERT", type any}, {"Risk Schedule Exposure", type any}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Text.Contains([#"Risk Levels#(lf)R1 = Project#(lf)R2 = Programme"], "R2")),
    #"Appended Query" = Table.Combine({#"Filtered Rows1", #"Project 2", #"Project 3", #"Project 4", #"Project 5", #"Project 6", #"Project 7", #"Project 8", #"Project 9", #"Project 10", #"Project 11", #"Project 12", #"Project 13", #"Project 14", #"Project 15", #"Project 16", #"Project 17", #"Project 18", #"Project 19", #"Project 20"})
in
    #"Appended Query"

I was trying to amend the code to use one query to load the sources that are listed in the column "Filepath" as per your reply in the above link. I tried using 

let
Source = Excel.Workbook(File.Contents([Filepath])){[Item="FilePath"]}[Data],

But I get the following error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Any help would be greatly appreciated. @ImkeF is this something you can help with?

Many thanks

[ImkeF]

Hi @Anonymous ,

you should turn the steps that shall be applied in the same way into a function with the Filepath as the variable/parameter.

(Filepath as text) =>
let
    Source = Excel.Workbook(File.Contents(Filepath), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Project ID", Int64.Type}, {"Risk ID", Int64.Type}, {"Date Raised", type date}, {"Risk Theme", type any}, {"Risk Name", type any}, {"Risk Details", type text}, {"Risk Owner", type any}, {"Risk Open / Closed", type any}, {"Risk Levels#(lf)R1 = Project#(lf)R2 = Programme", type text}, {"Generic Risk / Project Specific Risk ", type any}, {"Opp / Threat#(lf)(based on Impact score)", type text}, {"Risk Impact Category", type text}, {" Risk Impact Rating", Int64.Type}, {"Risk Category Details", type text}, {"Probability", Int64.Type}, {"Probability Details", type number}, {"Priority", type text}, {"Matrix Rating", Int64.Type}, {"Cost Impact", Int64.Type}, {"Cost Impact Detail", type text}, {"Schedule Impact", Int64.Type}, {"Schedule Impact Detail", type text}, {"Planned #(lf)Risk Response Summary#(lf)Risk Controls", type any}, {"Response Type", type any}, {"Actions", type text}, {"Number of Open Actions", Int64.Type}, {"Post Mitigation Risk Impact Category", type any}, {"Post Mitigation Risk Impact Rating", type any}, {"Risk Category Details2", Int64.Type}, {"Post Mitigation Probability", type any}, {"Post Mitigation Probability Details", type any}, {"Post Mitigation Priority", type any}, {"Post Mitigation Matrix Rating", Int64.Type}, {"Post Mitigation Cost Impact", type any}, {"Post Mitigation Cost Impact Detail", type any}, {"Post Mitigation Schedule Impact", type any}, {"Post Mitigation Schedule Impact Detail", type any}, {"P6 Activities Impacted - WBS ID", type any}, {"Last Risk Updated", type any}, {"Risk Closed Date", type any}, {"Cost Monte Carlo Probability", type any}, {"Min Cost Impact", type any}, {"Most Likely Cost Impact", type any}, {"Max Cost Impact", type any}, {"Cost PERT", type any}, {"Risk Cost Exposure", type any}, {"Schedule Monte Carlo Probability", type any}, {"Min Schedule Impact", type any}, {"Most Likely Schedule Impact", type any}, {"Max Schedule Impact", type any}, {"Schedule PERT", type any}, {"Risk Schedule Exposure", type any}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each Text.Contains([#"Risk Levels#(lf)R1 = Project#(lf)R2 = Programme"], "R2"))
	in
	#"Filtered Rows1"

Name this function "fnGetExcelData".

Then add a column to your table where you call this function, takting the column with the Filepaths as input for the function parameter. Fill in the following code into the window that pops up:

fnGetExcelData([Filepath])

[Anonymous]

Thanks @ImkeF,

I've created the new function, but I'm struggling with adding the column to call the function.  (does it matter that I'm doing all of this in Excel? rather than BI desktop?). I have got this far..

Do I add the column to the table where all of the file paths are stored / or the table where the data is imported to? Thanks

[ImkeF · Accepted solution]

Hi you have to add that column to the table with the column "Filepath" in it. So next to the filepath-column there will be another column that extracts all the data. You then just expand that data.

See this blogpost where I did it for URL instead (but it's almost the same principle (using adding column by function instead of an ordinary function, but you can use that technique as well)

😞 https://www.thebiccountant.com/2018/03/22/web-scraping-2-scrape-multiple-pages-power-bi-power-query/

[Anonymous]

Thanks @ImkeF I have now got it to work. Thanks very much for your help!