Forum Discussion
Transforming mulitple sheets in multiple files when using 'Load from folder'
Hello KWilkinson02
Try to do as follows
- Query one Excelfile in order to have the result you want
- Create a new text parameter calles "FileName" with the complete path of your Excel-file
- enter advanced editor and substitute in the first line the path information with your newly created parameter
- right click on the query and select create function. Give it the name "GetData"
- Query your folder
- Add new column invoking your function like GetData([Path]&[Name])
- Combine the new created column to one table
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello KWilkinson02
- enter advanced editor and substitute in the first line the path information with your newly created parameter
I can't do this to my final combined query only the constituent queries - am I doing this correctly?
--> you need to add the new parameter name in the report/query you created in the first step (where on Excel-file is completely formated)
- Query your folder
Get a bit lost here - do you mean 'Get data from folder' or something else?
--> as you told me your starting point is a folder with all Excel-files... so here is meant to query exactly this folder so that you have a table with all your files
Jimmy
Thanks for persevering here!
The final formatted Excel file is a combination of queries. The M code looks something like this;
let
Source = Table.NestedJoin(#"Project Information", {"Index"}, #"Project Milestones", {"Index"}, "Project Milestones", JoinKind.LeftOuter),
#"Merged Queries" = Table.NestedJoin(Source, {"Index"}, #"Project Stage Reviews", {"Index"}, "Project Stage Reviews", JoinKind.LeftOuter),
in
#"Expanded Project Stage Reviews"
Sorry, I feel like I'm being a bit thick here but where do I add the FileName parameter?
Hello KWilkinson02
this is 1 query with multiple steps.
You need a query where you query a Excel-file. This query doesn't query a Excel-file but it invokes another queries output
Jimmy
Ha, okay, nearly there (thank goodness I hear you cry!!).
The last bit about invoking the custom function. Not done this before either... when you say 'GetData([Path]&[Name])' does 'Path' and 'Name' relate to the relevant columns containing the data? The attached image shows what I've put in just now but it's returning an error (if I put in an absolute path it doesn't but (obviously?) it just duplicates the data... )
Hello KWilkinson02
use the function "Add custom column" and paste there the code GetData(......
Jimmy