Chuyên mục

Introduction to Power Query & Power Pivot Data Model in Excel 2016 (Excel Magic Trick 1468)

Rate this post

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
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:


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.

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?

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.

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.

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….

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *