Microsoft Excel Power Query (Get & Transform Data) Introduction - Part 1

[@leeching1]

The best video I've ever seen, so easy to follow and understand, spoke clearly and was very thorough and explanatory so easy to follow.

[@MyExcelOnline]

Wow, thank you!

[@MyExcelOnline]

💻Access 1,000+ Microsoft Excel video training tutorials covering Formulas, Macros, VBA, Pivot Tables, Power Query, Power Pivot, Power BI, Charts, Financial Modeling, Dashboards, Word, PowerPoint, Outlook, Access, OneNote, Teams & MORE! JOIN TODAY TO ADVANCE YOUR EXCEL SKILLS 👉 www.myexcelonline.com/107-42.html

[@ramasila7140]

Thank you! simple and clear explanation.

[@MyExcelOnline]

You are welcome 🙏

[@waddahnasher7747]

that was a great stuff man, thanks for the simple and straightforward manner.

[@MyExcelOnline]

You are welcome and I hope you now have more confidence to use Power Query 💪

[@raymondpang4138]

Having looked at many youtube videos on power query & power pivot, I want you to know that your video was the one that enabled me to better understand the content, flow of material , & what was going on. I am most fortunate to run across your video, as you have given me more enthusiasm & determination to learn the subject. I plan to view your follow up videos. Raymond Pang

[@MyExcelOnline]

Hey Raymond, I'm glad you learned quickly from our videos! Make sure to subscribe to our blog over at www.myexcelonline.com/blog

[@boohooboo]

From the moment you opened Course Flat File.xlsx into PQ... my mind was blown. I've been working so hard on stuff like this, sporadically, for years. Thanks so much!

[@MyExcelOnline]

You are welcome and we are glad that we rocked your world! Let us know if you have any questions. Thanks, John

[@shoaibrehman9988]

Sir you have a great job very easy and clear way of teaching thanks keep some more about power pivot 2016

[@MyExcelOnline]

You are welcome Ali. You can learn more about Power Query on our Academy course here: www.myexcelonline.com/107-51.html

[@humanresourcemtzion1561]

You're my tech wizard you have made my power query tool and pivot table so understandable. super special and insightful.Thank you so much

[@MyExcelOnline]

You are welcome and feel free to share this Excel Power Query video with your friends :)

[@hazemali382]

Amazing Mr many thanks

[@MyExcelOnline]

You are welcome and I hope that Power Query can help you in Excel!

[@hazemali382]

Thanks Oz ♥

[@MyExcelOnline]

You are welcome

[@imamfarooq1]

Very nice keep it up good work

[@MyExcelOnline]

Thanks Imam, keep on EXCELling!

[@amaraturner5764]

Very informative

[@MyExcelOnline]

Thanks Amara 👍

[@monicahaigh4490]

Great information, great presentation, great value. Thanks for sharing.

[@MyExcelOnline]

You are welcome Monica and I hope this Excel Power Query tutorial can give you lots of ideas of what is possible! Cheers, John

[@ElsaAndAnnie]

Excellent video with demo of power query. Looks like a very powerful tool. I do not think we need Microsoft access anymore. Please correct me if I am wrong. Thanks a lot.

[@MyExcelOnline]

Access still has it strengths, but Excel has come a VERY long way! And you are right, it is really powerful connecting other data sources.

[@paulseldn]

great video. Thank you. I didn't even know power query came with excel . Bless :)

[@MyExcelOnline]

Power Query is available in the latest Excel versions under the DATA tab and inside the GET & TRANSFORM group!

[@crzzyglu1970]

Great video! We receive SSN with and without “-“. Also double last names with and without “-“ Ex: Smith-Jones or Smith Jones

[@MyExcelOnline]

In that scenario you may need a few extra manual checks to make sure that the data is displayed correctly

[@TheSacnep]

Hi i like your video tutorial i am facing the problem with date which format is ~42564% so what is the best solution with this date format and and how to deal with missing values ?

[@MyExcelOnline]

Hi Sachin, it is difficult to check the problem without seeing the actual file to understand better how it became in that format. However are you able to change the column type to a date?

[@stanykempeneers8716]

Just saw this video today for the first time ! How well explained this is ! Never used Power Query before, but intend to use it frequently going forward. Unfortunately, already running into error with my first trial. Trying to upload data into a new *.xlsx file., by "Getting Data" "From File", it only shows "From Workbook" but my original data (downloaded from SAP) is an *.xls file, not an *.xlsx format. Error message : "UNABLE TO CONNECT", "WE ENCOUNTERED AN ERROR TRYING TO CONNECT", "DETAILS : EXTERNAL TABLE IS NOT IN THE EXPECTED FORMAT". Any suggestions on how to handle this ?

[@MyExcelOnline]

Hey Stany, glad you liked the video! You should Save As your Excel file as an .xlsx file and then try again the Import. Cheers, John

[@stanykempeneers8716]

@@MyExcelOnline John, obviously this is what I tried before sending my question. The report I used for testing is a download out of SAP. I download a dozen of SAP reports every day, which I have to clean and manipulate before using it. I can only download as an. xls (not .xlsx) or txt. I tried both formats, even saved my xls file as xlsx but without result. Still an error when trying to insert in Power Query.

[@MyExcelOnline]

@@stanykempeneers8716 This article may help you out: social.technet.microsoft.com/Forums/lync/en-US/df02e679-a65e-4d9c-90e5-05074770dfa7/dataformaterror-external-table-is-not-in-expected-format?forum=powerquery

[@richardjpanarojr9490]

Great video, as I have a file currently that represents remarketing data but needs to be flattened! The end of your video on same has now provided me with the intel to execute!! Thanks a lot!

[@MyExcelOnline]

Glad we could help you out Richard :) Thanks, John

[@andyjosolyne8744]

Very good introduction to the capabilities of PQ. A question if a flat file has in the top row the Year (2016) and in the next row the month (Jul) is there a technique with PQ of concatenating the two cells so that we have Jul 2016, and then remove the two rows leaving the column headings with Jul 2016 ready to be 'unpivotted'. Regards Andy

[@MyExcelOnline]

Hello Andy, Yes this is possible! Turn your flat file into an Excel Table (CTRL+T) and import it into PQ Editor. Then you need to select the two columns and under the Add Column tab choose Merge Columns. Under Separator choose Space and press OK. Close & Load to see it in your workbook. Let me know if this helps. Thanks, John

[@andyjosolyne8744]

John hi, Many thanks for your response., it is not columns but rows that I want to combine, e.g. in row 1 and columns contain 2016 , and in row 2 Jan, Feb, Mar etc in separate columns, so by the merge rows = Jan 16 in Col A, Feb 16 in Col B, Mar 16 in Col C etc. My belief is that it does not have this feature. Regards Andy

[@MyExcelOnline]

Andy, You will need to select the columns whilst you are in the Power Query editor and go to Transform > Unpivot Columns. This will fix your issues. Let me know if this is not the case. Thanks, John

[@olufemiaifaturoti215]

Hi Please where is the specific worksheet for this video tutorial?

[@MyExcelOnline]

It is located in the description area. John

[@Serraomomma]

The data I find is hiddeous! There could be numbers with text. separate groups of numbers with space delimiters, there are unnecessary inserted rows you name it i find it. love watch you help me find ways to set this stuff on fire! Burn!

[@MyExcelOnline]

Roxanne Serrao Hey Roxanne, Feel free to send through your workbook to us so we can have a look at it and ease your data pain: [e-mail] Thanks John