Data Cleansing Training Bonus Video #2: Transform an ERP Report to an Excel Pivot Table

Author: John Michaloudis - Posted on September 28, 2015

Home » Uncategorized » Data Cleansing Training Bonus Video #2: Transform an ERP Report to an Excel Pivot Table

The Ultimate Excel Power Query & Data Cleansing Course

is Closing its Doors in…

BONUS VIDEO #2:

Transform an ERP Report to an

Excel Pivot Table Dashboard

In this training you are going to learn the following:Intro: Converting Financial Data into a Flat File
01:00 Starting the Solution
04:50 Add Conditional Column in PQ
07:10 Unpivot Other Columns
08:42 Insert Pivot Table
09:28 Insert Slicers
10:28 Intro 2: Pointing to a Dedicated Folder
16:25 Add New File to Dedicated Folder
17:08 Refresh Pivot Table
17:27 Add another File to Dedicated Folder
18:50 A word about Queries, Connections and Filepaths
20:00 Editing the Query

DOWNLOAD THE EXCEL WORKBOOKS USED IN THIS VIDEO

Click to view & access the course now:

View Training Videos 1, 2 & 3 again before they disappear in:

* NEW ANNOUNCEMENT *

Listen to Oz and I discuss data cleansing & our upcoming online course…

Please leave a comment below if you liked this video or if you have any questions and we will reply back with a fix 🙂

(make sure to be specific and attach example workbook links if possible)

Facebook

Twitter

About The Author

John Michaloudis

John Michaloudis is the Founder & Chief Inspirational Officer of MyExcelOnline!

You May Also Like

Leave a Comment Cancel reply

I always Faced problem when I got the Report from Customer’s ERP,Nice and very helpful Videos,Thanks lot

Oz du Soleil says:

October 1, 2016 at 1:01 AM

You’re welcome, Kavita!

Hi Guys,

Thank you very much for this free course!
I finished the whole course and was mainly triggerd by the many opportunities that powerquery provides.
I’ve reccomended the course to some of my collegues and will surely look into your lessons and other opportunities in the future.
Keep up the good work!

One question: Is it possible to disable the case-sensitivity? This really bugged me when calling the Excel.Workbook([Content])

Greetings from Holland,

Marc

Oz du Soleil says:

September 30, 2016 at 5:25 AM

Marc, thanks for sharing your experience–with me and John, and with your colleagues.

The case-sensitivity is one thing I plan to address in an upcoming video. It’s a reason why I describe PowerQuery as being a bit unforgiving. Excel formulas and VBA code will help you with capitalization. But you don’t get that with PowerQuery or M code, and there’s no way of disabling the capitalization.

A lot of us are really bugged. And next month during the MVP summit at Microsoft, I’m sure I’ll be one of many people who’ll bring this issue up to the Microsoft engineers.

Is there any workaround for “conditional column” in PowerQuery 2013?

Oz du Soleil says:

September 30, 2016 at 5:43 AM

You can write IF statements in Power Query. Conditional columns are just an interface for making and nesting IF statements.
Here’s a video on it. Warning: get ready to get funky!

kool

Oz du Soleil says:

September 29, 2016 at 10:06 AM

VERY

Is it possible to call a query from a query? Would it have been possible to slightly modify the first query by deleting the load steps and leaving the transformation steps and then call it from the folder query?

Oz du Soleil says:

September 29, 2016 at 12:46 AM

This is something I’m digging into right now. I’ve heard that it’s not possible to have a query launch another query, but it’s possible to have a query pull data from another query, thereby causing that other query to do its job. It’s like pulling instead of pushing. This is something I’m playing with, and will share the knowledge once I have a decent understanding of it.

Regarding modifications of steps in a query, that can be dangerous. The underlying M code that’s recorded is very literal. Small changes can cause the code to crash, and the editor isn’t very good at pointing to what’s wrong.

It’s definitely doable to modify a query. It’s got to be done with full consideration of everything that’s going on.

Really nice technic… I found another one here (http://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/) that let to create you a custom function. In this way you don’t have to move in the folder first one file and then the others.

I have a question. Is it possible to extract and combine data if one (or more) of the file has multiple sheets and all the sheets have the same structure, but different names.

I used a workaround, so making several copies of the file and leaving inside only one sheet and renaming the sheets in order to have always the same name, but I think it should be possible directly, or not…

Oz du Soleil says:

September 28, 2016 at 9:04 PM

Yes. This is possible.

This image may help. When the Navigator comes up, highlight the folder rather than the objects below the folder. This will bring in all worksheets, and their names won’t matter.

Eric

What can I say after I tell you again how great this instruction is. Very useful and explained so well.

Thank you
David

Oz du Soleil says:

September 28, 2016 at 8:12 PM

Thanks, David!

Oz,

Wonderful content. Power Query is so powerful and your live (life use) examples are so much more meaningful than the usual generic warehouse data that is supplied in other presentations.

Oz du Soleil says:

September 28, 2016 at 8:06 PM

Thanks, Brendon. I’m glad you appreciate that.
You’ve connected to something that I did think was important: real life situations that aren’t as pretty as standalone tips & tricks. And the problem I notice people have with pivot tables is their data isn’t ready to go into a pivot table … because the data isn’t clean.

So, it’s been important to add a heavy dose of real-world into the lessons.

Buen día
Gracias por el monumental aporte, he aprendido y quiero seguir aprendiendo.
Cuando haya un curso en español seré el primero en inscribirme.
Reitero mi agradecimiento

Oz du Soleil says:

September 28, 2016 at 8:15 PM

Buen día
Me alegro de que ha aprendido mucho. Gracias por dejarnos saber.

Un curso de español. Se obtiene hizo pensar acerca de la forma en que se pueden hacer realidad.