Download Excel File #1: Download Excel File #2:
Download Zipped Folder:
Entire page with all Excel Files for All Videos:
Buy excelisfun products:
Learn about how
Topics:
1. (00:16) Introduction
2. (00:51) Discussion about the two different Data Ribbon Tabs and Get & Transform groups that you may see in Excel 2016
3. (03:51) Example #1: Clean Data and create Excel PivotTable Report, then add new data and refresh report!
4. (17:06) Example #2: Transform Cross Tabulated Table into Proper Data Set
5. (21:50) Example #3: Import 1.2 Million Rows of Data from Text Files into Power Pivot and create a Data Model PivotTable, then add new data and refresh our report.
6. (22:34) Diagram to understand Data Model and Power Pivot
7. (46:46) Unhanded Exception Error.
8. (48:02) Summary
Reference Video:
Highline Excel 2016 Class 03: Data Analysis Fundamentals: PivotTables, Power Query & Data Model
Tag: power query excel 2016, Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Excel Data Model, Power Pivot Data Model, Power Query, Clean Data, Transform Data, Power Query Import Data, Import Text Files into Excel, Big Data in Excel, PivotTable, Clean Data Power Query, Introduction Power Query, Introduction Power Pivot, Busn 218 Class, Highline Spreadsheet Construction, What is Data Model, Relationships Replace VLOOKUP, Excel Data Analysis, Business Intelligence
Xem thêm: https://meohay360.com/category/internet
Nguồn: https://meohay360.com
44 trả lời trong “Introduction to Power Query & Power Pivot Data Model in Excel 2016 (Excel Magic Trick 1468)”
I have watched many tutorials over the last 15 years and I must say this rates in the very top. I hope your other ones are as good.
I am not saying this because I necessarily learned anything new, though I did pick up some cool tid bits I never realized and thanks for that, it is because you method, demeanor and you examples allow you to understand and learn. You are a patient and walk through even the minor steps that others just assume you know. You are a teacher. Well done. Keep up the good work. I will be referring all my colleagues to this video.
First of all, just want to say your videos are top notch, thanks for all the content; I have learned so much from your videos. I have been experimenting with making power queries with Get and Transform and your videos and reference material is the best I can find. Anyway, hoping maybe someone here can help me out…what queries should I put in the Data Model when I create my queries? I have just scratched the surface of using the Data Model and the Power Pivot features, but further down the road, I want to learn more and take advantage of that, but for now, the solution I am working on doesn't "need" it. However, as I was building my power queries, I was selecting "add to Data Model" each time I closed and loaded a new query with the thought, "I want to use that later, so I will add it now." Now, I seem to be getting error messages whenever I learn something new and go back and improve my queries and then try and refresh them…something about the Data Model couldn't find, or some sort of issue saving back to the DM…SO are some tables not appropriate for the data model. How can I remove whether or not a query gets loaded to the data model after it has been closed and loaded? How can I add a query to the DM after I have closed and loaded it? Anyway, any insight, references, or just if words of encouragement would be appreciated. Thanks.
How do I thank you for all the great videos you have made?? You have truly transformed my career and I am sure thousands of other people!! I wish you to soon reach 1 million subscribers on your channel!!!
Hey Mike. Is this the first power query video of the series – I'm asking because I want to start form the beginning.
Haven't been using power query ever before.
Amazing the way excel advanced is taught. Thanks a lot.
It's like im learning Excel features from Nicholas Cage
Thank you for this brilliant technique.
Thank you so much for doing this! I love learning new things and you have to be the best teacher I have ever seen! I do have one question. During the video you mentioning going under file>options>data tab to change always in tabular form. I don't seem to have a data tab. I have microsoft excel 2016. Where should I look to find this?
Thanks again Mike for another great video!
We don't have the option Edit in 365 ( Your Video time 28:56) We have Transform Data Option.
Thank you! Is this the only video you have on Power Query or you have other videos also on Power Query? Thanks
Simply great 🙏
Excellent video with step by step instructions. Loved it. subscribed the channel.
At 46:00, I encounter an error, saying Unhandled exception has occurred in a component in your application. If you click Continue, the application will ignore this error and attempt to continue. Object reference not set to an instance of an object.
dayta
I really like the way you teach, starting with the nice overviews before diving in so that one gets perspective and knows where they are going. I was not able to download the data and hope that gets fixed OR that I figure out how to download it. Clicked on the links and waited for downloads.
N lastly have also subscribed to this wonderful channel.
Always my guy Mike making life easier in the excel world.Kenya is locked.Thanks to you and @Leila Gharani am almost considering myself an advanced user soon to graduate to that much coveted expert user.
this is very useful
Excuse me, Sir, I have a question. Would please answer it for me.
Why should I move the file (First) that contains (txt sheets) to the (Desktop) rather than keep them in the location where I saved them?
By the way, I tried to make them download in (PQ) from that location it didn't work. only when I saved them in (desktop).
I hope my ENGLISH is understandable, I am from Iraq.
With all respect.
This (small window) at (Unhandled Errors) showed up to me because instead to work on (File First) on (Desktop) I worked in another saved location in my PC. Then I lightened up.
You are a great teacher, sir.
Very good presentation with many situations and utilities covered ! Thank you !
Watched this video 3 times and have learned so much, it greatly helps me with a project I am working on! Thank you very much!
Sir I have 2016 office home and student I can't see manage data models on my ribbon how to have it
Showed error sir load to data model failed for both final sales table and and parameter 1(2)
Error message was the connection couldn't be refreshed because the connection string was invalid
I love the enthusiasm.
I wish that YOUTUBE has a second like button.
Hi, how can I contact you – I have a workbook with 3 tables – Instead of doing a lookup I was trying to do a power pivot by going the data model route. Yet I am getting errors. How can I send you the workbook for some help? Thanks
Very informariv evedios.
Thank you for the amazing work! I love your channel and recommend it to everyone I know 🙂
You are amazing. 👍
Thank you very much. You are very consistent.
Thanks again mike!
I am glad I have found your site. Thank you very much for your videos. You are an excellent instructor.
Great Video.
However, I do have Excel 2019 64 bit and when creating the pivot table and adding the column "Region", I still see "Column Labels" and "Row Labels", not the actual "Region" and "Month" respectively as mentioned in your video. Why is that?
Thanks a lot for sharing it. I will binge watch the entire series of lessons.🤓🤗
Hello, have you ever used a power query to import and clean data from an IRS Wages and Income Transcript? I need help on this matter. The document is 160 pages of stock sells. I just want the F1099-B data from the transcript.
Thanks so much. This gave me the answer that I was looking for to build a new DB
Good work
Awesome Power Query, Power Pivot, and Data Modeling explanation in a nutshell. Thank for that.
Hello. First of all I want to thank you for this tutorial but I'm having issues. When I am importing data (Only Creat Connection + Add this data to the Data Model) all the rows (1.168.377 rows) are loaded but it keeps on trying to load and never stops. Why does this happen? I'm using Excel 2019. Thank you.
I have 20 .csv file in a folder and i want merge all with power query…but problem is all file names are ending with .csv_ (i.e. reader7.csv_ and reader8.csv_ )…please help me how can i remove all underscores from all file simultaneously….because without removing underscore power query is not detecting format….
Thank u soooooo much…..
Thank you for your passionate and awesome tutorial and thanks for the worksheet!