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
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
Hurrah!!
Thank you so much for your patience, I have learn a lot this morning. I need to go and practice this a bit but your help has been invaluable! I'll mark the first response as the solution - others following can pick through the rest if they need to. 😁
Cheers
K.