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.

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!

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

You are making it look so simple. Well done!

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

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

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

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

Very clear, very helpful, thank you!!

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

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

Very useful .

lot of thanks sir

Just amazing

Well Done!!

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

great work

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!

Thank you so much, very useful stuff! :)

Good. Thank you!

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! :-)

Awesome!

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

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

Awesome 🤣🙏

Mashallah sir

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

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?

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.

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

👏👏👏👏

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.

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.

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.

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.

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

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

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" ===========================