Data Cleansing Training – Different Ways to Format Data Using Power Query

Is there an easy way to make name formats the same when you get file that has some names as Last, First and some as Last,First and some as First Last?

I still need another replay,lots to learn

Love your data clean up. My biggest problem is cleaning up Financial Data. For example QuickBooks Financials by month. The left columns are indented by GL Name and Totals and Sub-Totals. The monthly values are in columns with Month, Year headers. I want to do trend analyses to forecast the future but more at the summary level.
To get this into a flat file I have to:
1) delete empty columns.
2) fill empty fields with the above main category
3) remove totals
4) combine categories
5) then try manipulate the columns into workable fields.

Clearly if there was an easy way to build flat files then I can extrapolate and produce reports in Pivot Tables. Any thoughts?

Hi John,

Thanks for this video training program, I would be always keep watching and following you over here.

Regards,
Kuldeep

I downloaded the Power Query for my Excel and tried to back up on the video to catch something I didn’t understand but now cannot get the video to move forward… I’ve used instructional videos many times but have not experienced this issue.

I have three headcount Excel files at any given time, a budget; current quarterly forecast; and actual roster, each of which has about 6,000 rows, one per employee. I want to build a pivot table, but I need one table that has every Dept ID that can be any any of the three files. Along with the Dept ID, I need the Department description, which is also in each of the files. Lastly that table of unique Dept IDs needs to be assigned to a Parent and Grandparent Department, for drill down ability in the pivot table. What is the best way to extract all of the unique Dept IDs and Descriptions from all of the files?

Thank you so much for sharing your brilliant knowledge.

I have download power Query , not sure if I did it correctly. I am using excel 2010, did not see the same ribbons that are showing in your video.

Thanks,

Wow!
Wednesday I watched Oz’s video on Power Query and used John’s instructions to install it on my work computer.
Thursday I downloaded a data file from our ERP system and immediately used Oz’s excellent instructions to clean it up in no time!
My thanks to both of you for this.

Thanks, very helpful video. Thank goodness for the pause button

Great video, I really liked the tip about determining the number of unique rows via pivot table. The biggest problem I’ve been having is when using Power query to merge and having incorrect values in my merge fields, like text in a numeric. Is there a simple way to “see” the values in a ~3000 row table of numerics to determine if its clean?

I absolutely love your style. Power query is not called power query for nothing.

I have just started using Power Query with salesforce. It is very powerful for extracting data direct from salesforce tables. I can emulate the ‘AND’ function simply by applying filters to different columns. How do I emulate the ‘OR’ function in Power query?

For example I have 2 columns; ‘Town’ and ‘Start Date’. How can I filter records, for example, where ‘Town’ = London OR ‘Start Date’ > 01/01/2016.

In Excel I would use; OR(Town=”London”,Start Date>”01/01/2016″). Can I do this in Power Query?

Wow, who new life could be made so much easier with Power Query. I’m on to our IT Dept at work to see if we can get the Add In. Thanks for the very clear explanations.

Short, sweet and effective.
Installed without a hitch.
Thank you

First, really good video. Thank you.
A question: when you talk about additional data being received, for example, a new site for the classes, did you append that data to the old data? Or can you take a new report, somehow delete the old data and paste the new? I guess I’m really asking, “what are you “refreshing” in the query?”

As to an ongoing problem: every month I receive a report with 54 worksheets, all similarly structured — GL data for 54 operating centers. Is there a way to use Power Query to automatically load all of the sheets into Power Query. As it is now, I run a macro that moves all of the data into one sheet.

Thanks, I look forward to the next videos.
George

I am semi retired and have worked in government for most of my working life and am finding it harder to be considered for interviews etc. I have worked with spreadsheets since the 1990s but my knowledge of new versions of excel needs improving and so to keep me interested I have enrolled in two on-line courses (Excel Campus & Exceljet). Some content I already know but there is much about excel in the newer versions from 2010 on wards that I don’t know. Now that I’m approaching 63 years of age I keep my self interested by punting on the horses with a good friend who I worked with way back in the 90s. He’s good at VBA and I’m more familiar with the data analysis side and guess what the racing data is the key to our success.

Here is an example of the data we need organise for our daily punting activities:

http://www.racingaustralia.horse/FreeFields/Form.aspx?Key=2016Sep14,NSW,Warwick%20Farm

As you can imagine writing VBA is very time consuming and from your video I think we could clean this kind of data more easily using excel power query but we run live race by race vba exce and bet write up to the start of the race (ie withing seconds of the start) taking into account last minuet changes in odds and looking for the best bookmaker price. So that’s it really. Until I can break back into the part-time workforce this is my pass time. Hope your videos and tips can help. Cheers, Michael

Thank you very much for the video. It helps me lot and saves my lot of time .

Now I start exploring more on Power Query.

Hi Oz,
I just watched your video presentation on Power Query now and was so amazed. I have not heard of Power Query but do hear about Pivot tables and rarely use it. From your demonstration and presentation, its very interesting to see how all those years of time consuming work that I had done could be done very quickly and save a lot of time. I used to work in an institution and taught basic Excel for beginners and also work in the administration of the institution where data collection and manipulation is vital to produce reports and interpret and present to board meetings and management. I need to watch more of your videos to explore more techniques to enhance my performance. I was just smiling when you mentioned about the lady that had issues with data coz that was exactly what I faced. I depended entirely on an IT professional to produce those reports but the problem was that he did not give me what I wanted! Thank you for this video session and look forward to the next videos. I will now install Power Query on my computer.
Thank you!

Hi Oz,

The tutorial was nice and easy to follow. I have a vendor report from an ERP system used by a not for profit org that I am really keen to use the power query tool, and to clean up the data and make use of the information. This tutorial is awesome timing for me as I am tasked with some clean up projects. If I have any issues or need help would it be ok to ask for some assistance?

Thanks Deb

Power Query is the newest arrow in my quiver. Very well done and communicated. Thank you.

It was really amazing! Thank you! Looking forward to the other videos

Excelent video! Thank you!

The conversion from Quickbooks report into a flat file was the most useful part for me. I wish I should have known all this before.

https://www.dropbox.com/s/7vwqwsa02lsxtnu/CPR_EST.xlsx?dl=0

1. In my opinion this tuition is well done and allows a relatively easy absorption of the matters discussed; thank you.
2. Encouraged by your invitation, I enclose a worksheet with “original data” (is one of the outputs of Metastock, a stock analysis software). This data includes items in the text form ( as per the “test” section”) while I need actual numbers to handle subsequent analysis. The first issue is that the import of this file into Power Query does not generate the conversion of the text data into numbers, as explained in the video. Nor the alternative methods of multiplying by 1 or going through the text to columns procedure, have success. The second matter is that , after obtaining true numbers, I would need to reorganize the information as indicated in the “desired output” section. I could of course attempt to prepare a macro (with some difficulty); but I wonder if among the numerous tools offered by Power Query there is an easier solution to my issue.
May you kindly help?
Thank you
June 17, 2016

Thanks OZ,

Its really a very nice and informative session.
I am working of one of the report , where I have to refer around 10 different files(xls & txt) and based on comparing all the files, I am preparing the final report.
Right now I am using some trick and formulas(mostly lookup 40-50 times) and in few parts VBA macros.

I am looking forward, if via power query and from next time it will be just a click matter.
If you can add more tutorials about , how to import files from log files and lookup with multiples files based on multiple conditions.
than it will be a great help for me.
Thanks in advance.

sincerely,
Rajender

Thanks oz that has been of great help. Very much looking forward to ur next tutorial.

Hey Guys,
These examples were total awesome.
I’m working with this Power Query more and more, almost daily so this class is very help!
Thanks again!

Great video. Thank you. I’m working in Excel 2016 too but when opening your example files I get a compatibility Waring: The queries in this workbook might be incompatible with your current version of Excel. These queries were authored with a newer version of Excel of Power Query and might nog work in your current version.

Hi Oz!
Thank you for the video and for spending your time trying to improve our knowledge 🙂

I try to use PowerQuery at my work and I am pretty sure I am the only person who does it (unfortunately)! I am a beginner but I am always eager to know more on this subject. Currently, I have a problem importing SAP files. I would be glad if you could help me.
I have an excel file with, let’s say, 500 rows with the raw materials we use in production. It happens, that some of these raw materials had a change in their SAP codification, and now, I have two different codes for the same material….and I have to manually update the older codes to the new…there some way I could do this with Power Query at once?
Thanks in advance for your help.

Thank you and thank John and please keep doing this amazing work. I am self-taught and enthusiast about excel and data managing and analysis so resources like this are very valuable for me and for lots of people, I suppose!

P.S. I am writing from Portugal.
Helena

Will be this videos series available to download?

How can I reuse a Power Query? I create a formatted file and paste in the data that I have to clean up first. Is there a way that I can create the query then reapply it to the raw data the next time I get a data dump?

Good .. Class..

Hi,

Firstly that video provides a simple but enlightening introduction to Power Query. Thank you very much.

Secondly are you able to transform a table into a flat file using Power Query?

Kind regards

Craig