Power BI - loading multiple sources to one query

[danigod]

Hi everyone! 

I am trying to figure out how to load multiple excel files into one power query. I know you can load them by uploading a folder (https://docs.microsoft.com/en-us/power-query/combine-files-overview), but because I have 14 sources with 5 tabs each the code is very long and complicated with room for multiple errors. Each of these 14 sources is edited by 14 different people which adds more room for error. 

I am trying to use two "let" statements to upload the sources but I receive a "Token Eof expected" for my second "let" statement. Essentially what I have below. Is it possible to get an output like this? Or am I forced to do it the other way?

let
Source = Excel.Workbook(File.Contents("C:\Users\dtgod\Downloads\Heat_Exchanger_1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Geometry", type text}, {"Trial", type any}, {"Configuration", type text}, {"Hot flow (L/min)", type any}, {"Cold flow (L/min)", type any}, {"Th,in (°C)", type any}, {"Th,out (°C)", type any}, {"Tc,in (°C)", type any}, {"Tc,out (°C)", type any}})
in
#"Changed Type"
let
Source = Excel.Workbook(File.Contents("C:\Users\dtgod\Downloads\Heat_Exchanger_2.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Geometry", type text}, {"Trial", type any}, {"Configuration", type text}, {"Hot flow (L/min)", type any}, {"Cold flow (L/min)", type any}, {"Th,in (°C)", type any}, {"Th,out (°C)", type any}, {"Tc,in (°C)", type any}, {"Tc,out (°C)", type any}})
in
#"Changed Type"

[#2]

@danigod 

In general syntax for nested let is like

let
    a =1,
    
    b = let
        Source = 2
    in Source,

    c = let
        Source = 3
    in Source

in
    a + b + c

but I see no one reason to use them in your case.

You may use From Folder connector and customize generated Transform Sample File query with handling all possible errors.

Or create functions with file name as parameter and call them one by one (automatically adding another column with it after the step when you have list of files; or manually) with combining results. 

[#3]

@Sergei Baklan

I had used a sample file before and it gave me too many errors and even larger room for someone to mess up the query by editing the other files. I am trying to find a way to add each new file as a new row instead of a column. The is a pre-existing file so the more I mess with it the more I have to fix it in other areas.  How could I go about creating a function?

[#4]

@danigod 

Afraid I didn't catch what do you mean under "to add each file as a row". If your files have exactly the same structure, i.e. same all have the same columns, but data could be entered differently, you still may use From Folder connector. Keep in Transform Sample File only two steps, call the file and promote headers. After that Combine all binaries, you'll have data from all files in one table, after that you may start cleaning it.

Using of function assumes exactly the same transformation for each file which is probably is not your case.

In general, to create the function start from the query. You may define parameter which is used within it, right click on query and Create function. Or manually add first row as here:

(filename as text) =>
let
    Source = filename & ...
in
    Source

[#5]

@danigod 

This is an old thread, but I think this was not properly replied.

The syntax let ... in ... is similar to a variable definition. After the let, you define expressions that can refer to each other and that will be used to compute the expression after the "in".

Several loading of sources can be included after the "let", this is perfectly allowed. It is to be noted also that the word "Source" has no special meaning in M language. Feel free to replace it.

As far as M (the language of Power Query), a code like this will perfectly work (but will completely confuse the GUI editor):

let
  Data = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  MoreData = Excel.CurrentWorkbook(){[Name="Table2"]}[Content]
in
  Table.Combine({Data, MoreData})

I define two expressions "Data" and "MoreData" that will each load a table and combine them after the in statement.

The GUI expects only a variable name after the in and will not interpret the different steps.

This would be GUI-friendly:

let
Data = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
MoreData = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
AllData = Table.Combine({Data, MoreData})
in
AllData

l

Hope this can clear some misconceptions for other users.