Master Data Cleaning with Power Query in Excel in 9 Minutes

I had never used a Power Query before watching this video. The videos make is seem so easy - Thank you!

your videos are always great!!! I am going to try it soon

How can i pass the message " we reached the end of the buffer"

Please i need help, how do i transform a few rows to column on power bi and it becomes as a a stand alone column with data derived from same table

Do the same steps apply in power BI as well since it has the same process in power query? im interested when having new excel files for additional new dates and data like what is mentioned around the last part about the automated process, can this be applied same with power BI or is this only for excel power pivot?

I've bought your power query, power pivot and dashboards course. Well worth it by far. Not only have I saved a tremendous amount of time, but set a new standard for how we use excel in my company. The courses are packaged in small, easy to digest pieces (2-5 min) that make it really easy to get through and implement right away.

Very nicely explained. Thanks for sharing.

M'am, I am learning new things from your videos and give new way of thinking Thanks
And i have a question that in Group By function, we select columns which we want to Group By and other columns will Automatically removed so we don't need to take extra step for remove other columns.
I just asking that am i right or wrong..

I love your videos. You are one of the best trainers I have found. I have one caution regarding the automatic "Change Type". I work with data that contains leading zeros. The auto change type believes they should be "number" columns and thus drops the leading zeros. I have made it a habit to remove the auto Change Type and control that myself.

Very easy to follow steps, great presentation. Thanks for sharing, I am trying to combine multiple worksheets, each work sheets having different header columns. For eg,
Sheet 1 has A,B,C has columns.
Sheet 2 has B,D,E has columns,
Sheet 3 has A,CF has columns,
Sheet 4 has E,G,H has columns,
I wanted to merge all 4 sheets together A,B,C,D,E,F,G,H is this possible in Power Query? Can you please share a technique if there is any?

HOLY COW! System analyst for 25 years. This is game-changing!

Would it be possible to get all the employees who worked three or more weekends in the row last year ? I have a simple attendance table (Emp_ID/Date) and I have to use power query. Thanks

Fantastic video for starting Power Query. Best I’ve seen for sure.

Learning and employing Power Query two years ago was a game-changer! And, your tutorials significantly elevated my knowledge and leverage of the tool to generate efficiencies and accuracy. Thank you!

Yes, this is powerful. But, is it available on the Mac? If not, this is a 4th reason why some people don’t use it.

When i paste charts and slicers in Power point by excel .charts works fine and show paste link option but when i do the same for slicer it does not work and shows not paste link and embeded option tell me how to add the slicer in Power point

😎

I used VBA to do this years ago. It worked great for me, but it was difficult to teach others how to use it. Power Query is much easier to use and teach.

How could I contact you?

Thank you for your interesting video. Wanted to try it for myself but unsuccessfuly.
Is there an option for xls file which is not well organised to filtering and analysis? My document is hard to filter as there are a number of merged cells with text. At the same time, the table is pretty massive and contains around 30 000 lines.
It would be great to know the better way for analysis.

While I am able to bring people to PQ, I still see way too much garbage in-garbage out, including business managers reporting on incorrect or simply irrelevant KPIs. Before bringing ppl into PQ a lot of work has to be done educating professionals about what is relevant data in a report, how and which KPIs to choose and measure, and last but not least maintain one version of the truth with a proper schema construct. Last month’s report becoming next months source is a wrong way of doing things and can be dangerously misleading with multiple versions of your master data floating around.

Amazing videos and great pace of presentation to learn. Many thanks Mynda. Your inputs are aprreciated and most valuable (and I learned something about the Sunshine Coast conditions!)

I just watched your video on how pros spot amateur charts, so I snickered when you moved the legend to the top of the chart. Even pros sometimes take the easy way :)

sum of max height in the pivot is misleading a bit... aggregation is done in PQ, I would change the pivot column headers....

This is a very useful tutorial! Thank you for this information.

PQ is the best! Thanks Mynda

Absolutely, this is one tool that excel users should know. Such a powerful tool that can work miracles with data. Surprising part is that it is 13 years down the line since it was introduced and a vast majority of the people are still not aware of its existence! Hopefully your simple video will induce users to try this power packed tool.

Great! Thank you!

Very nice video 📹 Thank you for sharing your knowledge with us

Mynda I personally can admit that your tutorial(s) on data analytics; specially related to Power Query have improved my NEW career in data visualization. You are the best....my SENSAI of business intelligence.

thank you so much as always,

mo

It's still too much of a pain on Excel for MAC (even though I have the latest version with a 365 subscription)

Is there a way to stop the PQ preview refreshing each time you add/change a step? I am getting frustrated with how much waiting around I have to do when creating or modifying some queries.

I like the idea of PQ but am starting to lose patience 😢.

Great explanation :)

Sharepoint tables is really a tough nut. If I use a sharepoint tables connector the query loads the hole document libary into storage (>100mb) befor it even starts fetching rows. I even tried filtering befor expanding. If I use sharepoint contents I can not see my table :(

I nowadays use PowerQuery and PowerPivot to teach coworkers the system of the ETL process and the functionality of datawarehouse (in the simplest form) as you mentioned in your tutorial. Great stuff - thank you very much indeed!

Amazing Mynda as always. You truly do excel 🫡

I want to learn vb sript I don't know coding

Power Query is a very powerful tool.

Is there any difference between PQ in Excel and PQ in Power BI? I overheard someone saying that it was more limited in Excel but I've never known that to be the case, at least not in terms of the transformations it can apply – not sure if it's more limited in terms of data sources it can connect to.

❤ PQ. It changed the way I use Excel

Hi Minda. As always another excellent tip from you. Just one thing: I do not see where to download the file. Thank you

You never cease to surprise - you make it look so easy and simple! Thanks !

Hi @mynda... Have been using Power query but came a situation whereby, the web page ask for login credentials. Does this translate I picked the wrong URL path. Please explore further

Best certification for excel to get hired?

Very well explained! :)

What if I have access to a PowerBI report / dashboard and I would like to make my own analysis in Excel using PowerQuery?

I was able to export the result of a filter (but not sure if it can also work unfiltered) and there were about 30k rows in the report and excel was taking forever. I think I either clicked "analyze in Excel" on the dashboard visual or something like that and it came in a form of a pivot table in excel with live connection to the database (which I do not know if I am able to copy the connection using other apps) but I'd like to transofrm it either with PQ or pandas in Python, just need to figure out how to get the raw connection to the database / query..

Would you have any idea?

Everyone that uses Excel should be aware of and using Power Query. I will share this video link.

You live in Caloundra?! Hello from the Glass House Mountains! Practically neighbours!

I used this exact method based on an earlier video from you to gather and track Covid data for my company. It really does help! Thank you for these lessons.❤

? Remember Excel 2.0.. 👍😎✊

What about a webpage that has multiple pages and the query cuts off after certain lines. Also the webpage has drop downs that need to be entered to get current data? I couldnt get it to work