Consolidate Multiple Excel Worksheets using Power Query Custom Functions

[@Cluni-80s]

I am not sure which one terrifies me the most: the amount of functionality within Excel that I was oblivious to, or the amount of knowledge you have that I am in awe of.

[@AccessAnalytic]

Hah, we’re all beginners along the way. I’ve been a full time Excel consultant and trainer for 17 years and still only know about 70% of Excel

[@ivosilva2796]

A whole new world everytime I get to crossroads at work and have to dig deeper online for information. Excel never stops to amaze me. Great video!

[@AccessAnalytic]

Thanks, you're welcome

[@ivangardiner66]

Absolutely awesome video! I have never been able to get my head around functions in PQ, you make it so easy. Looking forward to applying this technique loads going forward. Thank you so much

[@AccessAnalytic]

Glad to help, thanks for leaving the kind feedback Ivan

[@sedarathnadcd]

Wooow, i was finding this function all over the youtube, finally got it. thank you.

[@AccessAnalytic]

Great. Glad it helped Priya

[@cristianchelu1563]

You are making it look so simple. Well done!

[@AccessAnalytic]

Thanks!

[@90hsilva]

Very good video Wyn, I found an easy way to convert the query into a function awesome!!!

[@AccessAnalytic]

Thanks Humberto, glad it helped and thanks for leaving a comment.

[@GeertDelmulle]

Nice video. Here are some suggestions for you: • look into the function Excel.CurrentWorkbook to get your tables (just make sure you exclude the ones you don't want). It's easy. • remove unwanted rows before using unpivot. • try changing data type using Locale (EN-US?). On the audio aspect: try easing down a bit on the plosives and use a de-esser, either during recording or in post. Keep up the good work! Really enjoy it! :-)

[@AccessAnalytic]

Thanks for the tips Geert

[@MrWish332]

Wow, Thank You So Much Wyn, really very useful.

[@AccessAnalytic]

You’re welcome Vishal

[@DanielBula1964]

On the Date Change Type step, instead of extracting before delimiter, you could change to date/time type and then to date. It should work. Thanks for sharing your knowledge!

[@AccessAnalytic]

Great tip Daniel (as long as you add the Date change as a new step)

[@DanielBula1964]

@@AccessAnalyticYes sir, you got it!

[@bojanjovanovic756]

Thank you Sir, you saved me planty of time next week!

[@AccessAnalytic]

You're welcome Bojan, thanks for letting me know

[@IvanCortinas_ES]

Excellent video tutorial Wyn. Very useful and well explained. Thank you so much for sharing it.

[@AccessAnalytic]

You’re welcome Iván. Thanks for commenting

[@lasirena07]

Very clear, very helpful, thank you!!

[@AccessAnalytic]

You’re welcome Maricela. Thanks for taking the time to leave a kind comment

[@TravelRunner]

Great video, thank you. Do you have a video on how you made the dashboard you showed at the beginning of the video? Thanks!

[@AccessAnalytic]

Hi, I don't have anything specific but the general approach is in the last 2 minutes of this video.

[@louism.4980]

Thank you so much, very useful stuff! :)

[@AccessAnalytic]

Glad it helped 😃

[@realpulsecoin]

Love every little tips on the way, great content and presention 👍 #Greetings from Norway

[@AccessAnalytic]

Thanks for the kind feedback Rasmus.

[@hasankocyigit]

Thank you very much, it is really nice video. Just I want to ask you; if the source is different such as Excel file, how can we change the source step?

[@AccessAnalytic]

Thanks Hasan, if you’re looking to consolidate multiple sheets from a different Excel file then check out the 5 minute mark of this video kzbin.info/www/bejne/gaOWf2OBo9WEeLM I’m using Power Query in Power BI but the technique is the same for Power Query in Excel

[@xyfo8784]

3:49-4:55 create parameter ProjectName, 4:59- pull the list of range names for the excel workbook for the Cleanup function to run against, 6:16-6:41 create the Cleanup function

[@AccessAnalytic]

I'm not sure what you are flagging here?

[@Breezy89er]

Well Done!!

[@AccessAnalytic]

Thanks

[@accountsdepartment7444]

great work

[@AccessAnalytic]

Cheers

[@jazzista1967]

HI Wynn....great tutorial... quick question: in order to create the cleanup function is a 3 steps process: your transformations , parameter creation and then with that parameter you created you converted into a function? Could you skip the parameter and create the function directly after your transformations? Thanks for posting

[@AccessAnalytic]

Hi, thanks. You must have a parameter to create a function.

[@cheikhhakim5864]

lot of thanks sir

[@AccessAnalytic]

You’re welcome

[@TheSTEPHEN009]

Sir where do I find the practice workbook. Please help. If you don't mind it sharing as I found it useful as well as fascinating.

[@AccessAnalytic]

Hi, I've just posted a link in the description section

[@TheSTEPHEN009]

@@AccessAnalytic Thank you Sir..

[@9699975917]

Just amazing

[@AccessAnalytic]

Thanks Bhavesh

[@MrKamranhaider0]

Very useful .

[@largpack]

the next level is to consolidate multiple tables/sheets from multiple files - also possible with PQ

[@AccessAnalytic]

Yes absolutely

[@ajaysharma2061]

Awesome!

[@AccessAnalytic]

Thanks Ajay

[@Luciano_mp]

Good. Thank you!

[@AccessAnalytic]

No worries Luciano

[@Vizurraga]

I real want to master this. Could one do the same with Microsoft Power😀 BI? I'm barely starting my journey with Power BI and its one of the programs that I would like to eventually become very proficient in.

[@AccessAnalytic]

Hi John, absolutely. Power Query is the same in Power BI

[@elfridhasman4181]

Awesome 🤣🙏

[@khalidmajeed2886]

Mashallah sir

[@AccessAnalytic]

Thanks

[@MrKamranhaider0]

Secondly do it work if instead of named range we craete table using Ctrl+t on each sheet.

[@AccessAnalytic]

Yes it works the same with Tables

[@VMarius-gm2vd]

Hi, want to connect two product feeds in csv format to update a stock according to sku, automatically every 6 hours, and how do I make the resulting file can be exported live for the web site to use that data for stock update, thanks, have a great day.

[@AccessAnalytic]

Not to sure on that one. Maybe some VBA needed or Power Automate Desktop to automate the scheduling of the refresh and export.

[@RIMI9793]

👏👏👏👏

[@AccessAnalytic]

😀

[@MrKamranhaider0]

Sir when we load data through folder ,all sheets in workbook can be appended without declaring named range then what you say better way to load through folder which contain only on particular file.

[@AccessAnalytic]

I don’t quite understand sorry

[@MrKamranhaider0]

@@AccessAnalytic if we load data from folder option and add collumn "Excel.workbook(content,true) then even sheets are appended .Under this method you don need to manually create ranges for each sheet

[@AccessAnalytic]

@@MrKamranhaider0 check out this video at 5.00 minutes.. kzbin.info/www/bejne/gaOWf2OBo9WEeLM is this what you are referring to?

[@ahyongyong1728]

Recently i am facing "we didn't recognize the format of your first file ()...". Previously The same sharepoint folders and excel workbooks able to be imported with sharepoint folder connector just fine. Anyone facing similar issue? I am using power bi december 2022 version. I have tried using excel power query to open the same set of folder and files, and it worked.

[@AccessAnalytic]

Odd one. I'd post an issue here community.powerbi.com/t5/Issues/idb-p/Issues and also scroll down to Get Support powerbi.microsoft.com/en-us/support/

[@asjones987]

I love your work, but you start out saying Power Query needs named ranges or tables to pull the data in to PQ. I have done Power Query many times without named ranges. I had an series of Excel files (same format) that had sheets with multi-line headers and other stuff that was in "presentation form". I was able to tell Power Query to open a file and transform the headers and data section etc. Then put it all back together.... no tables/named ranges required. There may be some extra cleanup required but all is good :)

[@AccessAnalytic]

You’re right Alan, I should have been clearer that I was referring to running a query within the same workbook. When referencing an external workbook you can also get the sheet objects.

[@Amol51279]

Music at the end of video is too loud. It's deafening sound. Hurt a lot. Pl change 😞

[@AccessAnalytic]

Sorry about that. Thanks for watching to the end 😊

[@jianlinchen7978]

Creating a function in Advanced Editor directly looks more simply. " (tablename )=> let Source = Excel.CurrentWorkbook(){[Name= tablename ]}[Content], #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {" Resource Name"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] "TOTAL") and ([Value] 0)), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Date"}, {"Value", "Hours"}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date.1", "Date.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type time}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date.2"}), #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Date.1", "Date"}}) in #"Renamed Columns1" ===========================

[@AccessAnalytic]

Ok, thanks