Excel 2013 Power Query #02: Transform Data and Import Into PowerPivot or Excel Table

See how to:
1. (00:32 minute mark) Explain Data and how we will transform it
2. (01:35 minute mark) Import Excel Table
3. (01:58 minute mark) Remove unwanted columns
4. (02:10 minute mark) Split Columns to remove unwanted Product ID data
5. (02:54 minute mark) Remove column after split
6. (03:39 minute mark) Import Text File
7. (03:49 minute mark) Use First Row as Headers (because field names not interpreted correctly)
8. (04:25 minute mark) Merge Tables to create relationship or VLOOKUP Helper Columns
9. (05:21 minute mark) De-normalize table by adding two extra columns in Merge feature
10. (05:40 minute mark) Rename columns
11. (05:48 minute mark) Remove columns after merge is complete
12. (06:15 minute mark) Load to Data Model
13. (07:11 minute mark) Create Calculated Field with SUMX Dax Function to calculate Profit
14. (08:05 minute mark) Create PivotTable with Slicer

[excelisfun]

Excel 2013 Power Query #02: Transform Data and Import Into PowerPivot or Excel

See how to:
1. (00:32 minute mark) Explain Data and how we will transform it
2. (01:35 minute mark) Import Excel Table
3. (01:58 minute mark) Remove unwanted columns
4. (02:10 minute mark) Split Columns to remove unwanted Product ID data
5. (02:54 minute mark) Remove column after split
6. (03:39 minute mark) Import Text File
7. (03:49 minute mark) Use First Row as  Headers (because field names not interpreted correctly) 
8. (04:25 minute mark) Merge Tables to create relationship or VLOOKUP Helper Columns
9. (05:21 minute mark) De-normalize table by adding two extra columns in Merge feature
10. (05:40 minute mark) Rename columns
11. (05:48 minute mark) Remove columns after merge is complete
12. (06:15 minute mark) Load to Data Model
13. (07:11 minute mark) Create Calculated Field with SUMX Dax Function to calculate Profit
14. (08:05 minute mark) Create PivotTable with Slicer

[B_Bless]

Whoa your videos are easy to follow and you make it so entertaining. Your voice makes it more exciting thanks and God bless 🙏

[bubblesandhugs]

Thank you! I have a common key in each table and kept trying to create a relationship and realized from your video that I needed to merge the tables. Thank you!

[brianx]

you are the best! thank you for your lessons. Best on internet!

[Kumar-zgpm]

Excellent video and its very clear and straight to the point.

[BillSzysz]

Thanks for PQ and PP lesson :-)
You don't need edit the query if you want to change the name of it.
Right mouse click on a query and the last position is Properties  where you can change the name of the query and add some words about it.:-))

[igorb]

Fantastic info. Keep up great work. I enjoy your videos so much. I wish I could just inherit your Excel knowledge.

[paullang]

Use of Power Query is very impressive. Got a lot of learning to do to use it effectively but appreciate the good start from your videos. Question - have you tried to "Get External Data" from Quicken? The best I can do is produce a Quicken report and import the Text file. Would like to save the report production step if I can.

[soonc]

Can you use vlookup or index/match in Excel to lookup values from a table pulled in PowerPivot or data model in PowerQuery? You're using pivot table to reference those datasets but can you do formulas with them in Excel cells?

[thejorgemarques]

Hi, this is great, was needing this for ages, couple of concerns I have, what is the limitation in tables, for exemple I want to join a lot of tables together and this envolves a lot of merges queries, since I first transform the tables into queries to reduce the number of columns I need, but I am concerned at Data integrity If I made to much merged queries into a single query consolidating all tables/columns that I need, I have around 15/20 tables with around 6 columns each and 2000 rows!

Also any tips for big data volume as good practice?

Thanks

[mirrrvelll]

Videos are great as usually, I am following lots of them. Question, what is capacity of Data Model database if can store over million of data (rows)? How many files you can store there? I did not know actually about that option in Excel (never used) but since last period, I am facing lots of data (ongoing) and we are talking about over million. I realised that PowerQ when I am using it (if making chart for instance) from like 200k files, is soooo slow. Any idea?

Thank you!

[pingchu]

ExcelIsFun : Thank you so much for you all videos that help me to start working on power pivot and query. I just got a problem that need your or other expertises kind advise. When I got an external excel table loaded to Power Query then get it "load to" Table sheet that shown on current file. I found the 1st column always not showing ascending order sequence though the original table was in ascending order in 1st column. I also tried to do ascending order sorting in query before "load to" but it's still not succeeded. Could you pls help to advise how to fix it pls?

[sazzarello]

I've followed your channel and watched many a video and I couldn't be happier learning from you. I have a problem and googling is getting me nowhere. Can I "load to table" 2x tables on 1x excel tab, on top of each other by leaving enough space between them, without getting the error "This won't work because it would move cells in a table on your worksheet". It works fine until I add 1x row to the source of the query even though there are 500 rows at the destination. Load to A3 and load to A1400.

[THeRAPIST]

Can you please do something proper on Power BI Preview - it would be fantastic help! Also, is there a donate link?

[ArmandoReportes]

Wow. I am watching your video and thinking... Why am I using macros to do this?

[amrgaber]

can work with excel in company or freelancer ?

[alistairsmith]

what happens when excel says "limit one thousand records reached"? how do i make it bigger?

[josecruz]

Sorry, What does Alt, B, M stand for? I've got Power Query in a different language and it does not work well.

[harryzeritis]

Do you know how to export data from power query directly to a csv file? Exporting it to a table does not work....I guess my file is too big.

[baramasha]

Thank you for video. Could you pease hlp me with question: How can I add data from Power Query to Power Pivot in Excel 2010? Thank you in advance