Here is a how-to for importing Excel files from a folder using Power Query (Get and Transform). It’s fairly easy to do.

HOWEVER, in this example, I’m sharing a folder with someone who starts putting goofy stuff into the folder and they break the Power Query query and the process. Now what?

One move is confrontation. Tell the person to ” knock it off!” Another move is to modify the query.

Check out the video. Please comment. Share your thoughts. Ask questions.

The song in the video is “Quintal do Céu” by Seu Jorge

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

This is Crap data at its worst–a dataset that cannot be cleansed. This video is a dose of Excel data-cleansing reality.

Someone asked for help cleansing a column of dates so they could be sorted. As I explored the data in Excel and Power Query to see what was wrong, I uncovered deeper problems.

There weren’t just formatting issues. One date had a year “20165.” So many red flags piled up and this is when the responsible thing to so is STOP! This data cannot be trusted. Send the data back to the source for verification and validation.

BTW: you’ll also see use of the Locale feature in Power Query (Get and Transform).

Join me at Patreon: https://www.patreon.com/Data

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

This video is a wild one! To make running totals in native Excel is pretty easy. However, in this video, you will see the adventure for making Running Totals in Power Query (Get and Transform).

You will see some M Coding in Power Query, Lists, List.Range, List.Sum, custom columns, and putting an entire table inside a single column.

In Excel we can work at the cell level and that’s what makes running totals easy. But, in Power Query, we have to work on entire rows or columns. So, there are a lot of steps to force Power Query to give us what we want.

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

Download the workbook here:
http://datascopic.net/GTInstinct
LET’S WHOOP CRAP DATA!

This 5-question quiz is designed to expose you to Crap Data and test your instinct for what Power Query (Get and Transform) features you’ll need for victory.

The solutions aren’t in this video because we’re focused on INSTINCT–what you think you’ll need before you get started. However, the solutions are in the workbook that you can download here: http://datascopic.net/GTInstinct

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2