Forum Discussion
Power BI - loading multiple sources to one query
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 + cbut 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.
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?
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