Transforming mulitple sheets in multiple files when using 'Load from folder'

[KWilkinson02]

Hi,

I have a folder full of spreadsheets, each containing multiple ranges and sheets that I want to transform and then merge into a summary table.

Currently I'm having to load from folder, select the table or worksheet I want to transform, use the 'helper' to carry out the transform on all the excel files and then repeat the process, i.e. go back to loading all the files from the folder again.

This doesn't feel right.

How can I carry out the transforms I need for each table and sheet by only loading from folder once? Is there a way to duplicate and re-path the Transform Sample File helper?

Any help or pointers to tutorials gratefully received.

Cheers

K.

 

[Jimmy801 · Accepted solution]

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

[KWilkinson02]

---

- 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.. 

---

[Jimmy801]

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

[KWilkinson02]

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?

[Jimmy801]

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

[KWilkinson02]

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... )

[Jimmy801]

Hello @KWilkinson02 

use the function "Add custom column" and paste there the code GetData(......

Jimmy

[KWilkinson02]

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.

[Jimmy801]

Hello @KWilkinson02 

GREAT!! Finaly we got it 🙂

Thanks for the feedback

I'm glad that we made it

All the best

Jimmy

[KWilkinson02]

Hi @Jimmy801 ,

I have another question.  How can I update the query after it is created? 

I'm pulling data from Ranges within my data source and I'm finding if I alter a range my amalgamated data isn't updating.

Also if I amend the Query I can see the new data but this doesn't cascade down into the amalgamated data.  Equally, if I just make a change to the Query by say removing another column this also doesn't cascade down.  Is there some means to update the Function?  From the stuff I've read elsewhere, it sounds like it should update automatically... 

[KWilkinson02]

Ah, got it, I'd missed the 'Load more' option!

[Jimmy801]

Hello @KWilkinson02 

be carefull about the load-more option because if you really have a lot of files with different column names I'm  not hundred percentage sure that this option will check every table. Better check it every now and then

Jimmy

[KWilkinson02]

Thanks for the heads up.  In this instance I think it's fine because I'm checking specific tables/ranges where I know I've made changes.

[KWilkinson02]

Thanks @Jimmy801 I will give this a go today and let you know how I get on.  There some stuff here I don't quite understand but I'll do some digging and try and figure it out.

Thanks for taking the time to respond, much appreciated.

[Jimmy801]

Hello @KWilkinson02 

okay, great. Just ask if something is not clear

Jimmy