What is Power Query?

[@JuanIArana]

Brilliant explanation Wyn. I find PQ knowledge is a must nowadays! Thanks for sharing

[@AccessAnalytic]

Thanks very much @JuanlArana

[@AlphaWatt]

SO glad to have found your channel! A career change has me switching gears from creating models with Python to exclusively Excel. Your videos are getting me quickly up to speed. Thank you!

[@AccessAnalytic]

Glad to help, thanks for leaving a comment to let me know.

[@RaisingTheAnte]

I absolutely love Power Query. It's been a real game-changer in the way I've been able to consolidate data from a bunch of reports and shoot it out to my boss's in a way that they can understand. LOVE YOUR VIDEOS!

[@AccessAnalytic]

Fantastic !

[@louism.4980]

Thank you so much for this, what impressed me the most is converting the columns of dates to rows, something that is a massive headache to do on Excel using formulas.

[@AccessAnalytic]

Totally! You’re welcome

[@despaid1]

Just making a career change into the world of data analytics and this tutorial was amazing! This will be a total game changer for how I clean up reports and combine large files. You make it so easy to follow!! Thanks bunches!

[@AccessAnalytic]

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

[@yonootz2wheels]

good luck Desiree....I am going for the same career change

[@mdms2161]

The content and explanations are simple and straight forward. Glad I found your channel. Learning more from your videos then 6 months of short course on MS Excel. Cheers.

[@AccessAnalytic]

Glad to help Bata, thanks for taking the time to leave a kind comment

[@anilpothuru5651]

Matter is too clear Love from Indian🇮🇳 🤘🤘

[@salehmohamed3191]

Thank you sir so much for creating this video. Very well explained and useful for anyone needs to learn Power Query. You deserve a Noble Prize.

[@AccessAnalytic]

Hah, thanks. I appreciate you taking the time to let me know you found it useful

[@Devilchild7139]

Most useful. In fact incredibly useful. I have been taking Mynda Treacy's courses for a year and I just wanted a different perspective, not because she is inadequate, but because I get information overload when it all comes from the same person. I must like her work and her style because I have subscribed to further (Paid) courses! Your tutorials, however, do not just offer a refreshingly different perspective, but they add a new layer to my understanding. Well done. Brilliant presentations and great style. So many other Excel trainers are just so boring. Too boring to listen to in fact! I have subscribed to your channel and will investigate your company's offerings in more detail. What is it about Australia that makes two extremely talented people emigrate there? (Three if you count her hubby, Don) You are the only Welshman Excel VP that I have ever come across and you live in Aus!!!! Later on, I will come back to you about Excel Solver. As far as I can see from the records of the Operational Research Society (I am a member) I am only one of two or three Excel consultants in the UK who know what the hell it is and also knows how to use it!!

[@AccessAnalytic]

Thanks for the kind feedback, I’m glad you’re finding my content useful

[@joshuawilliams4191]

Thanks for this, really useful for my work ands will tell my coworkers about this channel so they can get their training in!

[@AccessAnalytic]

Good stuff!

[@mrsimonexeter]

Great tip with using a named cell to stop a table being generated when getting data from a cell on the current workbook

[@AccessAnalytic]

Cheers Simon

[@IvanCortinas_ES]

Bravo Wyn! Brilliant explanation. All your videos are very useful. Thank you.

[@AccessAnalytic]

Thanks for your continuing support Iván

[@chamanthawickramarathne2029]

The way you present seems precious!

[@AccessAnalytic]

😀

[@jusuflazami9580]

Super Duper Amazing explanation, clear and concise!! Awesome!!

[@AccessAnalytic]

😀 thank you

[@Karen-bm3rj]

I'm busy teaching myself power query, and your videos are very useful and helpful. Essential viewing 🙂

[@AccessAnalytic]

That's great to know, thanks for sharing Karen

[@myxammg]

You are great , you are not just person explain I think you are school for learning, thank you and please don't late to make new videos

[@AccessAnalytic]

Thank you very much Mohammed

[@a.achirou6547]

Excellent explanation and great teacher !!

[@AccessAnalytic]

Thank you

[@petecardona8203]

AWESOME explanations mate! Simple yet very insightful...

[@AccessAnalytic]

Cheers Pete

[@faisalmehmood6889]

Excellent transformation in a sec!. @AccessAnalytic where can I find these sample files for more of a practice side.

[@AccessAnalytic]

I have only recently been including files, I didn't keep this one

[@OlBelkan]

You are amazing! Thank you! I work with excel every day and I haven't known about it. I finished school 9 years ago, and I thought I was a pro 😅

[@AccessAnalytic]

You’re very welcome. Thanks for taking the time to leave a kind comment . When I was 32 I thought I knew 90% of Excel. Almost 20 years later I now know 70% 😀

[@wayneedmondson1065]

Hi Wyn. Awesome video! Even when it is review, I pick up tips and tricks from you. Thanks for all of your informative and inspirational videos, tutorials and blog posts :)) Thumbs up!!

[@AccessAnalytic]

Thanks Wayne. Always great to get your kind comments.

[@patrickmainoo3849]

Vert useful presentation and very insightful

[@AccessAnalytic]

I appreciate you taking the time to let me know you found it useful

[@flowerpixel]

Can't wait to binge on these 👍👍👍

[@AccessAnalytic]

Hope you enjoy them Holly!

[@khalidmajeed2886]

sir I am baby in power query now i dive into pole with you.....great sir

[@AccessAnalytic]

Learning Power Query is a fantastic journey Khalid

[@JayeshPatil-nl5uc]

Superb explaination. I want to load 7 different structured sheets into one consolidated file, then clean and transforming to load into SAP SAC. What should be my general steps? This work is repetitive every month. Which functions and software will help me most?

[@AccessAnalytic]

This maybe? kzbin.info/www/bejne/pGnCpHRsg7KfoaM

[@raselkhan1384]

I am astounded by the way you present these things. I am using Excel since 2010 but was always afraif of Power Query and Pivot Table. They seemed complicated, until now. However, is it possible to share the datasets you use so that I can practice?

[@AccessAnalytic]

Hi. I have some datasets here Thanks Rasael, you can get access here accessanalytic.com.au/free-excel-stuff/free-excel-templates/

[@raselkhan1384]

@@AccessAnalytic Thank you and best wishes. Looking forward to getting more tutorials.

[@tarikuwodajo1019]

Amazing explanation

[@AccessAnalytic]

I appreciate you taking the time to let me know you found it useful . Thank you 😀

[@CeliaAlvesSolveExcel]

Beautiful summary, Wyn! How hard was it for you to decide what to show about Power Query in this video?

[@AccessAnalytic]

Thanks Celia, I think unpivotting and consolidating are two killer features , and the simple data clean up inside excel is relevant to all. The challenge is what not to show as there’s so much good stuff! I was missing a few basic videos I can point people to who are curious

[@CeliaAlvesSolveExcel]

@@AccessAnalytic I agree with everything. I have a presentation on the same topic in a few weeks. I know the struggle.

[@harshitjain6047]

AWESOME SESSION SIR LEARNT A LOT AND WOULD LOVE TO LEARN MORE FROM YOU. 😇

[@AccessAnalytic]

Glad you liked it, keep watching! 😀

[@ExcelUnlocked]

great content as usual, are there any exercise file to follow along?

[@AccessAnalytic]

Thanks, I’ve only more recently started including my files sorry.

[@khalidmajeed2886]

mashallah....helpful for every one.

[@AccessAnalytic]

Thank you Khalid

[@rabidpocketmonster]

Genuinely love the use of Excel for the presentation instead of PowerPoint 😂 I need to learn that + trick

[@AccessAnalytic]

😄, yep I always wondered why people took so much time to copy paste their charts and tables into PowerPoint 😝

[@Zovi-kanu]

above brilliant you make analysis easy

[@AccessAnalytic]

Hah! Too kind 😀

[@iankr]

Many thanks, Wyn.

[@AccessAnalytic]

All good Ian 😀

[@equiwave80]

Thanks for this video. It was very helpful. It would be great if you mention where I can find the files that you used in this video for practice. By the way you have a new subscriber.

[@AccessAnalytic]

That's great Deepak. I don't have the budget file but the CSVs for consolidation are here aasolutions.sharepoint.com/:f:/g/AA_CPort/Eqq476I-mDFEtp5c64A2glUBf2KeDCNr4F6Zlil7lzZXrw?e=Cb2srF This was also used in this video: kzbin.info/www/bejne/hZWclqlvZbV1Z9k

[@aicx40]

Awesome, thanks for sharing.

[@AccessAnalytic]

You’re welcome

[@sudeep2580]

Great video. Thanks.

[@AccessAnalytic]

You’re welcome

[@haozhang1436]

Hi Wyn. Thank for you the video. A question do you recommend switching manual calculations in excel? I'm using power query to import multiple excel tables into an excel file and use those tables as source for a summary report. Several array formula/lookups/sumifs/filter functions on imported tables. I experience a slow refresh import from power query. Good to mention that the files are coming from an excel on sharepoint. Do you have any other recommendations to speed up the refresh ?

[@AccessAnalytic]

I'd look to do as much as I possibly can in Power Query and them use the Data Model also if building reports off the cleaned up data. In terms of speeding things up I have this video kzbin.info/www/bejne/Y4moaHt7j8eEn7s

[@haozhang1436]

@@AccessAnalytic thank you for the reply.

[@hankthompson8615]

Awesome video!!

[@AccessAnalytic]

You're welcome

[@abbaskassem1541]

Hello. Your explanation is wonderful. I have a an issue which I can't find any solution for it online. I have a table with millions of rows in power query with multiple columns. Is it possible to know if the items in column1 for example exist in column2 no matter if they are not located on the same rows? i want to return true or false next to each item from column1 so i can use this in my analysis whether in Excel or Power BI.

[@AccessAnalytic]

Hi Create a query with just column 2 in it and remove duplicates then Merge it with your original table and expand the new column. Then add a conditional column where null = False Where you get nulls there are no matches

[@abbaskassem1541]

@Access Analytic thanks a lot🙏🏻. So there is no a DAX formula that can do it? I always enjoy watching your videos ,your way of explanation is simple and straight to the point. Well done 👏🏻

[@AccessAnalytic]

Thank you. Better to create True False columns in Power Query rather than DAX unless you need to reference measures or separate loaded tables

[@myusqielfahmi8538]

TYVM sir, GBU and you are awesome

[@AccessAnalytic]

Thanks

[@jianlinchen7978]

to some extent, PQ provides a replace of some fuction of VBA.

[@AccessAnalytic]

Power Query has replaced ALL my VBA that related to extracting / transforming / Combining data

[@Luciano_mp]

Awesome, great. Thank you.

[@AccessAnalytic]

You’re welcome Luciano

[@alexrosen8762]

Excellent!

[@AccessAnalytic]

Cheers Alex

[@anaballesteros-sosa4007]

Thanks you. Very clear

[@AccessAnalytic]

You’re welcome Ana

[@Nitinpaste-k5l]

No1 "Mr.M.

[@zaheerahmad3896]

GOD bless you !😀

[@AccessAnalytic]

Glad to help

[@gopichand5717]

Thank you sir

[@AccessAnalytic]

You're welcome @Gopi Chand

[@strever5]

i'm stuck on adding a calculated date column. I'd like to add *fixed text* + CurrentMonth + CurrentYear (Month / Year calculated by formule). Any idea how to do this?

[@AccessAnalytic]

Something like [Text Column] & " - " & Date.MonthName( Date.From( DateTime.LocalNow() ) ) & " / " & Text.From( Date.Year( Date.From( DateTime.LocalNow() ) ) )

[@santoshadhikari9933]

Does it have to be the same Excel sheet for PowerQuery to work where PowerQuery steps? or Is this Query saved in every Excel Sheet we open from then onward we write this query How does that work?

[@AccessAnalytic]

Power Query code is saved in an Excel file. That code can reference external Excel files, CSVs, websites, databases etc. It can also reference tables and name ranges within the same Excel file. Hope that helps

[@mohammedhammadahmed2168]

Hi sir I have a query like I have a single excel sheet in a excel file in that single excel sheet I have multiple tables with different data and header is it possible that I make a relationship with those tables and make a DASHBOARD in power bi

[@AccessAnalytic]

Yes, you can use Power Query in Power BI to pull the data from multiple tables in a single Excel sheet and then create a relationship between them. Fro there you build your dashboard.

[@icameback1993]

Thank you

[@AccessAnalytic]

You’re welcome

[@TheJoshtheboss]

Is there an easy way to create a copy of file without Power Query? If I wanna share my file with 3rd party, but don't want them to have a query in the file. Is there a good and safe way to do it? So I would not remove query and oversave original file by accident?..

[@AccessAnalytic]

Open the Queries and Connections panel and delete the queries. If there are multiple then Hold Shift to select first and last, or put them in a group and delete the group

[@accountsdepartment7444]

Hi....Sir I have a serious problem while transform and edit pdf file from our Accounting Program "Pioneer"......PDF Accounting Ledger statement while converting to excel showing : Debit & Credit side showing in different columns . I mean in 1st page "Debit" shown in column 'A" but second page "Debit" Shown in "B" column so on.......same way "Credit; coloumn also shown in difference coloumn in each page. I means not in one line thought-out the sheet. Pls help me to sort out this issue ....

[@AccessAnalytic]

Hi I’d recommend posting a sample file and screenshots to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589

[@hazemali382]

Many Thanks but where's the Workbook to Practice ?

[@AccessAnalytic]

Hi, I’m more recent videos I’ve started to include links to sample files. I am unable to find the file I used for this one.

[@hazemali382]

@@AccessAnalytic Thank you vey Much Mr. Wyn 🙏

[@soodvarun20]

Would have been good if the practice file could also have been shared

[@AccessAnalytic]

I’ve started sharing files in more recent videos

[@mrabea8336]

please can you give me a road map for data analysis to study from your channel?

[@AccessAnalytic]

Hi, I don’t have a roadmap but learning Power Query with Data Modelling and DAX is a key concept 3 Essential Excel skills for the data analyst kzbin.info/www/bejne/f2K7lneJYqx_mMk I do have a book coming out in 2 weeks pbi.guide/ that teaches Power BI and has a chapter on the same concepts in Excel

[@abdulkadirt7242]

hey, can you give us the data you have used in your video?

[@AccessAnalytic]

Hi, I do add links to files in newer videos. I don’t have this one to share sorry.

[@abdulkadirt7242]

no problem you do great work.@@AccessAnalytic

[@tintingho7606]

Could u upload the practice file? Thank you ❤

[@AccessAnalytic]

Hi, I often add the files these days but that one I didn’t sorry.

[@sharadgupta2007]

Great

[@AccessAnalytic]

Thanks Sharad

[@faisalmehmood6889]

Excellent transformation in a sec!. @AccessAnalytic where can I find these sample files for more of a practice side.

[@AccessAnalytic]

Thanks, I didn’t used to keep the files and share links sorry. In more recent videos I’ve started including them.