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
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
- Query one Excelfile in order to have the result you want
my Excel file needs about 7 queries to get what I need - these are all merged into one report and this is working okay (in principle at least)
- Create a new text parameter calles "FileName" with the complete path of your Excel-file
I've done this and it's worked fine.
- 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?
- right click on the query and select create function. Give it the name "GetData"
I can do this if I carry out the step above on one of the constituent queries.
- Query your folder
Get a bit lost here - do you mean 'Get data from folder' or something else?
- Add new column invoking your function like GetData([Path]&[Name])
this might make more sense if I can get the step previously to work but not sure where I'm supposed to be adding the column...
- Combine the new created column to one table
Again, not sure on this one. I know how to merge queries but not tryed anything specifically with columns..
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