Тёмный

MSPTDA 14: Power Pivot Intro #2: Amazing Columnar Database Importing Millions of Rows Data to Excel 

excelisfun
Подписаться 1 млн
Просмотров 43 тыс.
50% 1

Download Excel START File: people.highline.edu/mgirvin/A...
Download Zipped Folder with Text Files: people.highline.edu/mgirvin/A...
Download Excel FINISHED File: people.highline.edu/mgirvin/A... and people.highline.edu/mgirvin/A...
Download pdf Notes: people.highline.edu/mgirvin/A...
Assigned Homework:
Download Excel file and complete the homework:
people.highline.edu/mgirvin/A...
Zipped Folder with Text Files: people.highline.edu/mgirvin/A...
Examples of Finished Homework:
people.highline.edu/mgirvin/A...
This video teaches about the Columnar Database in Excel Power Pivot Data Model, which allows us to import and hold millions of rows of data in an Excel Workbook and have a small file size. We can also make Data Model PivotTable Reports from this imported “Big Data”.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction: Discussion of how to import “Big Data” Into Excel. Discussion about how Excel, Power Query, Power Pivot and the Data Model Columnar Database work together to make importing big data possible in Excel (and in Power BI Desktop).
2. (03:25) Power Query to Import Text Files (Big Data) From Folder and Append them into a single table.
3. (07:50 Columnar Database Explained. How does Columnar Database work? Where is Columnar Database in Excel?
4. (12:55) Make Data Model PivotTable with Implicit Measures.
5. (14:23) Example of dramatic file size reduction when we use the Columnar Database for a single Text File.
6. (16:21) Define Columnar Database. Lookup at pdf notes.
7. (17:37) Why Microsoft names the Big Data tool in Excel “Power Pivot”.
8. (18:21) Summary

Опубликовано:

 

1 авг 2024

Поделиться:

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист
Посмотреть позже
Комментарии : 211   
@sallyg5hlee636
@sallyg5hlee636 5 лет назад
Your videos are the reason for a recent job promotion. I should probably pay you some type of commission/agent fee but... Please accept my sincere gratitude. These videos are absolutely amazing!
@excelisfun
@excelisfun 5 лет назад
Congratulations on your promotion!!!!! I am glad that the videos help! Sincere Gratitude = comment on every video that you watch and Thumbs Up on every video that you watch : ) Easy!
@dafeac
@dafeac 4 года назад
he has donate button, I think I will donate at least a coffee we owe him, right?
@LeilaGharani
@LeilaGharani 5 лет назад
Great explanation of columnar database! Thank you Mike.
@excelisfun
@excelisfun 5 лет назад
You are welcome, Leila!!!!
@ljubicar1987
@ljubicar1987 4 года назад
You are the best teacher, it is pleasure listening to you!
@spilledgraphics
@spilledgraphics 4 года назад
This is the only Channel using an easy-to-learn-language: "the Columnar "Database" thanks Mike!
@spilledgraphics
@spilledgraphics 4 года назад
Ok enlightening moment-os: 01:23 : "we Excel people", I even put my hand on my chest and raise my chin. :P 02:08 : the comparison is golden. 05:37 : great keypoints about this process. 06:17 : wow, cool feature to convert from numbers to date very fast! 09:03 : Mike, you should work for Microsoft, amazing diagram & explanation. 10:04 : wow (again)....an outstanding visual explanation of the columnar database 11:01 : great introduction Pertipaq Compression 11:45 : great explantion about Dictionary encoding 17:42 : nice history to know!
@malcorub
@malcorub 2 года назад
This makes way more sense today than when I first watched this in 2018.
@excelisfun
@excelisfun 2 года назад
That is good. and the tools are much more popular now too : )
@dhucaluk1
@dhucaluk1 3 года назад
Thank you again! Especially for not having ads interrupt. These videos are so valuable.
@sandip_bettereveryday
@sandip_bettereveryday 5 лет назад
You never stop amazing us! So much of excitement in every word you say!! Rediscovering the amazing tool again and again with you. Thank you.
@excelisfun
@excelisfun 5 лет назад
Glad to help you re-discover the awesomeness of this Columnar Database!!!! Thank you for your support, Sandip!!!!
@anisaini1979
@anisaini1979 5 лет назад
You r a great teacher, and I find your lessons to be clear, concise and very engaging. Thank you very much.
@excelisfun
@excelisfun 5 лет назад
Yoy are welcome very much! You can help support the cause with a thumbs up and comment on each video that you watch and a sub : )
@RobMichaels1
@RobMichaels1 5 лет назад
Thanks for demystifying the Columnar Database and its many synonyms in this instructive video. I look forward to your videos!
@excelisfun
@excelisfun 5 лет назад
You are welcome, Rob! Many more to come : ) Thanks for your support !
@woosafb
@woosafb 2 года назад
Simple explanation yet covered in-depth complicated concepts. Thanks a lot. It was amazing.
@excelisfun
@excelisfun 2 года назад
Glad you like it!
@Luciano_mp
@Luciano_mp 5 лет назад
Another very well explained and dynamic video, plus an excellent series. Thank you Mike!
@excelisfun
@excelisfun 5 лет назад
You are welcome for the dynamic video. The pdf notes are pretty good for this video too : ) Thanks for your support, Luciano!!!!!
@stevennye5075
@stevennye5075 4 года назад
Excellent, I am going through these videos after doing so last year and they are much clearer. I started with no background, but now it is making much more sense.
@reanalytics1863
@reanalytics1863 5 лет назад
I consult for companies and individuals and someone would think I attended the Microsoft Virtual Community or some high level excel school. I got it all from your channel, even paid courses from top rated sites could not provide me with what your channel has single handedly provided for me. I owe you much Thanks for your great contribution in many lives
@phoolkhan87
@phoolkhan87 2 года назад
Hi, I am learning from you since 6 years.... Salute to you....
@excelisfun
@excelisfun 2 года назад
Thank you : )
@roderickmose4691
@roderickmose4691 5 лет назад
I come for the information. Stay for the knowledge. Leave with the understanding. Excellent as always Mike!!
@excelisfun
@excelisfun 5 лет назад
Love your summary of excelisfun videos! You are welcome for the videos - thanks for your support, roderick mose, with your comment, Thumbs Up and Sub : )
@JoshuaDHarvey
@JoshuaDHarvey 4 года назад
Love that I've watched this a few times and every so often come back for a refresher if need be. Thanks for all your hard work Mike!
@excelisfun
@excelisfun 4 года назад
Boomeranging back is good : )
@mohamedreda5738
@mohamedreda5738 4 года назад
Dude!, I love how you make your videos and that you the only one that makes EXCEL actually FUN, you are my EXCEL Teacher, thanks a lot.
@excelisfun
@excelisfun 4 года назад
You are welcome! Excel is fun, mohamed : )
@RaviGupta-mo1nf
@RaviGupta-mo1nf 5 лет назад
Without you, I am Zero in Excel. Thanks Mike.....You are certainly the best -- at explaining EXCEL and as a HUMAN BEING
@himanshudalai1028
@himanshudalai1028 5 лет назад
Thank you Mike for this marvelous video !! A theoretical in-depth understanding of the concept of Power Query & Power Pivot !! Great master class explanation as always !!
@excelisfun
@excelisfun 5 лет назад
You are welcome, HIMANSHU!!!! It will keep getting more fun as we go on!
@MatusiSK
@MatusiSK 4 года назад
Mike you are a hero to many many people all over the world trying to get better job and simply have better life :) I'm watching your videos almost everyday! Helps me a lot in job as analyst. Keep it coming!
@darrengodkin
@darrengodkin 5 лет назад
As always Mike, great easy to understand explanations. Thanks for sharing these brilliant new techniques
@excelisfun
@excelisfun 5 лет назад
You are welcome! There is NO doubt, the new Power Tools are TOTALLY transforming the way we do things - and making it easier and more fun !!!!! Thank you for your support on each video that you watch with Thumbs Up and comments : )
@mohamedchakroun4973
@mohamedchakroun4973 5 лет назад
Always there is even a small detail to learn from you Mike. You talk in the video about millions of rows supported by excel, i hope that youtube will support my billions of thanks and thumbs up to you mike for what you are making. Not only I learn excel but my english speaking skills is getting better :-)
@excelisfun
@excelisfun 5 лет назад
Thanks for the billions of thanks and thumbs up, Mohamed!!!!!!
@akshayhandoo
@akshayhandoo 2 года назад
Thanks Mike. That was informative and helpful. Learning a lot watching your videos.
@GeertDelmulle
@GeertDelmulle 5 лет назад
Thank you Mike: this was concise, very well explained, action packed and with lots of visuals and story boarding. Just perfect! See you on the next one...
@excelisfun
@excelisfun 5 лет назад
Thanks for noticing the story boarding - it is fun to make : ) Thanks for your support, Geert!!!!
@ricos1497
@ricos1497 5 лет назад
Story boarding... something else to learn!
@excelisfun
@excelisfun 5 лет назад
I don't story board, literally, like when you plot out with pictures BEFORE the video. I plan the basic outline of the video, make all the data, create the files, then film the video, sometimes with 20, or 30 or more video files. Then as I edit, this is where I envision the full story with all the pictures, and it is during editing that type of story boarding really happens. Also, sometimes I get to the middle of the video and realize that the story is not correct, and so I have to start over. Then, sometimes I have to start over multiple times. So, as you can see, the story boarding is quite messy and all over the place : )
@ricos1497
@ricos1497 5 лет назад
That's quite a process! It's interesting to hear the effort that goes into these videos, and much appreciated of course. I've done training for users before and it never really struck me that the effort I put in to that would be similar every single time you do a video. Impressive really.
@GeertDelmulle
@GeertDelmulle 5 лет назад
ExcelIsFun What some may call a messy proces, others will call iterative incremental development, often abbreviated to just: “agile” ;-). Long story short: the end result of the videos are top quality and super effective as learning tools, and in the end that’s what it’s all about. And don’t forget all the supporting materials... We thank you for it!
@user-ju1it8wf2j
@user-ju1it8wf2j 4 года назад
Wonderful intro to PowerPivot. Thanks a lot.
@enriquedominguez9709
@enriquedominguez9709 5 лет назад
Thanks Mike. Clear and cleaver as allways, it really reinforce my foundations on Power Pivot.
@excelisfun
@excelisfun 5 лет назад
It will keep getting better and more fun as the videos roll on : ) Thank you for your support, enrique, with your comments, Thumbs Up and Sub : )
@evgeniam8882
@evgeniam8882 4 года назад
Thank you for teaching us amazing information, even behind the scenes explanations to fully understand how it works!! So much appreciated! thank you beyond words!!
@excelisfun
@excelisfun 4 года назад
You are welcome, Evgenia!!! And thank you for your beyond words support with your comments and thumb ups on each video that you learn from : ) I just wish there were more viewers like you.
@kamranb1369
@kamranb1369 5 лет назад
Thanks, Mike. Great introduction to PowerPivot
@excelisfun
@excelisfun 5 лет назад
You are welcome, K B !!!!! Thanks for your support : )
@chrism9037
@chrism9037 5 лет назад
Power Pivot rocks. Great video Mike, as always!
@excelisfun
@excelisfun 5 лет назад
It does rock!!!! Thanks for the support, Chris : )
@ADfamily81
@ADfamily81 2 года назад
thank you Mr. ExcellsFun you are an incredible talent
@wayneedmondson1065
@wayneedmondson1065 5 лет назад
Hi Mike.. thanks for the awesome video on PQ/PP. It is mind blowing to be able to manipulate that much data so easily on the desktop. Thumbs up!
@SyedMuzammilMahasanShahi
@SyedMuzammilMahasanShahi 5 лет назад
Amazing video as always. Thanks Mike for another EXCELlent video.
@excelisfun
@excelisfun 5 лет назад
You are welcome, Syed!!!!
@knikl
@knikl 4 года назад
Power Pivot is amazing just like your videos. Thanks again!
@excelisfun
@excelisfun 4 года назад
You are welcome, again, knikl!!!
@shoeshines2121
@shoeshines2121 4 года назад
Wow! Microsoft has really upped its game between Power Query and Power Pivot! I can't wait to learn more about the nuts and bolts of Power Pivot and the DAX formula language.
@kamransiddiqui4506
@kamransiddiqui4506 Год назад
Hats off! To you. What a video. Amazing.
@mattschoular8844
@mattschoular8844 5 лет назад
Excellent explanation. Thanks Mike
@justinfazzio
@justinfazzio 4 года назад
I got super excited learning about how loading only the connection into the data model would support millions of rows... something that I've wanted to do for months for my company's big data store that I am analyzing, however, when I tried it, Excel gave me the dreaded out of memory error and crashed. This is the reason I avoid Power Query and Power Pivot when using big data. The amazing Dax functions and capabilities are precisely what I need but my company doesn't allow its thousands of employees to use 64 bit Office only 32 bit and my guess is the 32 bit is not enough to handle the millions of rows. I've had to resort to Access which is much more stable yet not nearly as flexible and powerful as PPivot and Dax. Nevertheless, this is still a superb video and your instruction and preparation is really second to none. What is really evident in your instruction is just how well you understand how each thing works. Thanks a dozen times over for all of your great videos.
@excelisfun
@excelisfun 4 года назад
You are welcome, Justin!!!! I am sorry to here about your company and 32 bit. Maybe you want to download the free Power BI Desktop and do you Biog Data Analysis in that. Videos 16 in this class shows how.
@JSUG4219
@JSUG4219 5 лет назад
Great video, as usual, Mike! Thanks so much!
@excelisfun
@excelisfun 5 лет назад
You are welcome, Jack!!! Thank you for your support : )
@filipsvakjaroski20
@filipsvakjaroski20 3 года назад
Thank you Mike :) Excited for the next video.
@excelisfun
@excelisfun 3 года назад
Yes, the next video is epic : )
@anisdadani8980
@anisdadani8980 4 года назад
You have tremendous value in my knowledge and my output
@alfreduu
@alfreduu 4 года назад
Hey man! Your videos are really awesome. Thank you very much!!!! Congratulations from Brazil!!!
@excelisfun
@excelisfun 4 года назад
You are welcome, Rafael from Brazil : ) I hope you can help support my efforts to bring free Excel Education to the world with thumbs ups and comments ion each video : )
@ivanrybalchenko7225
@ivanrybalchenko7225 4 года назад
Thank you, Mike! Great explanations as always!
@excelisfun
@excelisfun 4 года назад
You are welcome!
@samiasultana3477
@samiasultana3477 4 года назад
Mike G. You are the Excel Institute to me!
@excelisfun
@excelisfun 4 года назад
Glad to help, Samia!!!! Thanks for your support on each video that you learn from with a comment and thumbs up, and of course your Sub : )
@armondnazarian4455
@armondnazarian4455 4 года назад
Another very well and clearly explained video, thanks!
@excelisfun
@excelisfun 4 года назад
Glad it is clear for you, Armond!!!!
@krishnamurthy7721
@krishnamurthy7721 2 года назад
Much much appreciated..great work God bless you keep good work.
@excelisfun
@excelisfun 2 года назад
Glad the content that I post helps : )
@MalinaC
@MalinaC 5 лет назад
Thanks for great explanation of columnar database!
@excelisfun
@excelisfun 5 лет назад
You are welcome, Malina!!!!! Thanks for your support : )
@olegyes934
@olegyes934 5 лет назад
It's absolutely awesome! Thanks a lot!
@orcawilly54
@orcawilly54 4 года назад
Thanks for you great videos.
@shubhampawar8506
@shubhampawar8506 3 года назад
Very well explained ❤️..thanks a lot🙏
@excelisfun
@excelisfun 3 года назад
#14 MSPTDA and onto #15, an epic Power Pivot video : )
@MrThapaliya233
@MrThapaliya233 4 года назад
Brillant as Usual!
@excelisfun
@excelisfun 4 года назад
Glad you like it, Mahesh!!!! Thank you for supporting my efforts with your comments and thumbs ups, and of course your Sub : )
@davebowman5392
@davebowman5392 5 лет назад
Thanks Mike for another great video.
@excelisfun
@excelisfun 5 лет назад
You are welcome, Dave - Thanks for your support : )
@Howtoexcelatexcel
@Howtoexcelatexcel 5 лет назад
Power Query & Power Pivot = awesome, great video as ever Mike. Regards
@excelisfun
@excelisfun 5 лет назад
Glad the video is great for you, Hoe To Excel At Excel.Com!!!! Thanks for the support : )
@eladiobardelli3001
@eladiobardelli3001 5 лет назад
Eres un máster, gracias por tu pasión x Excel. Saludos desde Chile from end the World.
@excelisfun
@excelisfun 5 лет назад
¡Bienvenidos por la pasión! Gracias por el apoyo de esta calle en las Américas, Chile !!!!! You are welcome for the passion! Thanks for the support from just down the street here in the Americas, Chile !!!!!
@naveedazam1445
@naveedazam1445 4 года назад
Absolutely masterclass
@excelisfun
@excelisfun 4 года назад
Glad you like the masterclass, Naveed!!! Thanks for the support with your comment, thumbs up and of course your Sub : )
@chanman201
@chanman201 4 года назад
good job very useful. i'm learning PowerBi but this translate better with my background in excel.
@johnborg6005
@johnborg6005 5 лет назад
Thanks Mike. Looking forward to some DAX :) :)
@excelisfun
@excelisfun 5 лет назад
Me too!!!!!! Thanks for your support, John : )
@stevennye5075
@stevennye5075 5 лет назад
another excellent video
@excelisfun
@excelisfun 5 лет назад
Glad you like the Columnar fun, Steven! Thanks for the support : )
@alperkins66
@alperkins66 11 месяцев назад
Superb training!! the only item that is confusing (maybe previous video #1) is when importing data, Excel365 no longer has "Edit" icon...just the "Transform" icon. Thank you Mike!!
@excelisfun
@excelisfun 11 месяцев назад
That's right. This class is good, but I did make it almost 5 years ago. There are definitely some user interface differences, especially in Power BI.
@msamysobih680
@msamysobih680 2 года назад
You have changed my life for ever .. since I started watching this series I feel I'm ruling the world of my company's data :D Thank you, you are doing a great job by making the life of other people better by miles. I have a question here, since this way of storing data is much more efficient .. is it possible to import the data from a source (TXT , many tables, sql db or any other source) then unlink the columnar DB to have an independent static snapshot. What I'm doing now to take a snapshot of my data using DAX studio, but I'm storing them on TXT or CSV files .. which are huge files as you may expect because I'm dealing with millions of records. If this question is already answered in a later video I hope you can let me know which one to jump to it now :) Thanks again.
@douglaszulu6281
@douglaszulu6281 5 лет назад
great video Mike
@excelisfun
@excelisfun 5 лет назад
Glad you like it and it has use, Douglas! Thank you for your support with your comment, Thumbs Up and Sub : )
@pmsocho
@pmsocho 5 лет назад
Great video! Thanks!
@excelisfun
@excelisfun 5 лет назад
You are welcome, Teammate!!!
@NoShadowOfDoubt1
@NoShadowOfDoubt1 5 лет назад
Can’t wait for part 3
@excelisfun
@excelisfun 5 лет назад
Me neither! I have been working on the outlines out the video all day. It will be epic. But as is often the case, I will probably try to fit many topics into one long video so that the story flows, but it will be a lot of cool material. Thanks for your support on each video, NoShadowOfDoubt!
@NoShadowOfDoubt1
@NoShadowOfDoubt1 5 лет назад
Your prep and setup examples to each video are amazing and something to learn from also...thank you!
@NoShadowOfDoubt1
@NoShadowOfDoubt1 5 лет назад
Your prep and setup examples to each video are amazing and something to learn from also...thank you!
@excelisfun
@excelisfun 5 лет назад
You are welcome!
@khaledseghari1189
@khaledseghari1189 3 года назад
Great video thank you
@excelisfun
@excelisfun 3 года назад
You are welcome!!!!
@rrrprogram8667
@rrrprogram8667 5 лет назад
Its becoming interesting day by day... Love to watch ur videos I recently had 3 million rows... And then i used dax... With Iterating function filter... And measure was taking unusually long time.... So sometimes... Optimization is also the key... Awesome to follow along ur videos
@excelisfun
@excelisfun 5 лет назад
Glad that you love to watch! I love to make these : ) What was your DAX Formula with iterating FILTER that was taking a long time on the 3 million rows? What does the Data Model look like?
@rrrprogram8667
@rrrprogram8667 5 лет назад
Data model is very simple I have a DimDate table and fSales table the objective of the exercise is to find the "avg warranty cost" of different products during its life cycle, at various periods .. say ... avg warranty cost of products which completed 6 months (since sale date) similarly for "avg warranty cost" for product completed 12 months, and for 18 months fSales table has usual common header names "Product name", "Product Serial number " , "Sold Date", "Failure date", "warranty amount" DimDate has one to many relationship with fSales connecting "Sold Date" column... Hence, measure to calculate avg warranty cost of product completed 6 months ~ 180 days... is as follows.. CALCULATE ( [Avg Cost], FILTER ( fSales, fSales[Failure Date]
@excelisfun
@excelisfun 5 лет назад
What is the [Avg Cost] Measure?
@excelisfun
@excelisfun 5 лет назад
This is iterating over Fact table: FILTER ( fSales, fSales[Failure Date]
@excelisfun
@excelisfun 5 лет назад
Rather than use FILTER over Fact Table in CALCULATE, I wonder if we could use: DATESINPERIOD(dDate[Date],LASTDATE(dDate[Date]),-180,DAY). This way the dDate Table is used and the filter will flow to Fact Table, rather than have FILTER iterate over Fact Table.
@m.sz.120
@m.sz.120 5 лет назад
This video reminds me of the Power Pivot standalone broadcasts. Only this one is a bit more detailed on the explanation part, while those ones are more learning-by-doing. Strong on DAX as well, later on. Thank you.
@excelisfun
@excelisfun 5 лет назад
Yes, the videos I made about PowerPoint for from 2014 - a long time ago. Things have changed a bit, since then... Lots more to come in this series. Thanks for your support, M. SZ.!!!!
@socheatnhoeb1239
@socheatnhoeb1239 2 года назад
thank you so much
@excelisfun
@excelisfun 2 года назад
you are welcome so much!!!
@tomrhodes785
@tomrhodes785 4 года назад
amazing!
@excelisfun
@excelisfun 4 года назад
Glad it is amazing, Tom : )
@malvin74s
@malvin74s 5 лет назад
Thanks a lot!
@excelisfun
@excelisfun 5 лет назад
You are welcome a lot, Rodrigo!!!! Thanks for the support for the excelisfun channel with your comments, Thumbs Ups and your Sub : )
@Golfina19
@Golfina19 2 года назад
Thanks!
@excelisfun
@excelisfun 2 года назад
You are welcome! Thank you for your kind donation. It helps me to keep making free Excel and Power BI Education for the World!
@cmaman1
@cmaman1 4 года назад
Thank You Sir
@excelisfun
@excelisfun 4 года назад
You are welcome, M. A.!!!!
@ahammadali4440
@ahammadali4440 3 года назад
Thank you
@excelisfun
@excelisfun 3 года назад
You are welcome!
@RobMichaels1
@RobMichaels1 5 лет назад
Hi Mike, I just noticed the October Power BI update includes a new Preview feature called "Enable column profiling" which looks very interesting and I am sure you will make a great video about it!
@excelisfun
@excelisfun 5 лет назад
I have not tried the feature yet, what does it do?
@RobMichaels1
@RobMichaels1 5 лет назад
When in PowerQuery edit mode it shows column statistics such as %error or %empty rows and number of unique or distinct values per column.
@excelisfun
@excelisfun 5 лет назад
Wow! I can't wait to try this : ) I just had a query with many errors and I did not see the feature. I will have to try again : ) Thanks for the hot tip. Rob!
@alwarhi
@alwarhi 5 лет назад
Amazing
@excelisfun
@excelisfun 5 лет назад
Glad it is amazing for you, Thanks for the support!!!! : )
@Apatchi92
@Apatchi92 Год назад
Great as usual Mike, I have one question that how can we use multiple arrays into the UNIQUE function, for example = UNIQUE(array, [by_col], [exactly_once]), here I need to use multiple array rather than one array.
@stephanweaver1960
@stephanweaver1960 4 года назад
I rarely remove specific columns; I keep specific columns. The only exception is merging; I remove the (duplicate) key in the merging table. Inputs change and they're more likely to change on the non-essential columns; I rather not refer to them in any way inPQ
@alexandreletourneux9581
@alexandreletourneux9581 5 лет назад
Great as always !!! Thank you =) May I ask you the following. I have a pivot table generated from Power Pivot. Rows show my clients' name, columns month (Jan-Dec) with a Grand Total column for Rows (sum of 12 months sales), and sales amount in values. Is there any mean to show only an additional Grand Total column with would be summarized by Average or Max, etc.. but without changing my display for monthly columns. What would you please suggest: writing an Avg fx outside of pivot (with ISBLANK fx if rows from pivot are filtered) ? any DAX measure which would just show as Grand Total ? Excel can be as fascinating as brain-teaser at the same time ^^
@ismailismaili0071
@ismailismaili0071 5 лет назад
thank you so much Mr. Mike you are right the data model makes the work easier to load more in fact i use it now in the same 10 years data then that makes my friends feel it's magic they were asking me where is the data that you brought these info from hhhhhhhhh, until now i don't tell them.
@excelisfun
@excelisfun 5 лет назад
hahahaha... It is magic!!!!!!!! Excel and Power Query and Power Pivot and Data Model and Columnar Database and Power BI Magic!!!!!!
@shaikhjaveed5598
@shaikhjaveed5598 4 года назад
Thanks
@excelisfun
@excelisfun 3 года назад
No problem : )
@pittedmetal
@pittedmetal 4 года назад
Thanks:)
@excelisfun
@excelisfun 4 года назад
You are welcome, pittedmetal!!!!
@davida.taylor8444
@davida.taylor8444 5 лет назад
Very interesting and useful information. I always wondered how Power Pivot stores the data and how Vertipaq works. Great examples! Is there a way to actually view the columnar database?
@excelisfun
@excelisfun 5 лет назад
Not that I know of. Glad the video helps, and thanks for your help and support with comment, Thumbs Up and your Sub : )
@phoolkhan87
@phoolkhan87 2 года назад
Now learning power edition of excel....
@excelisfun
@excelisfun 2 года назад
Yes!!!! : ) I am glad to help you : )
@w57d
@w57d 3 года назад
Size of Columnar Database - Load To Sheet vs Load Connection only Hi Mike, I am huge fan of your awesome and inspiring videos. I'm importing tables from a MS Access genealogy database, and after seeing MSPTDA 14 I did a little test. My MS Access mdb file is 50.340 KB. I load a bunch of tables (15) with Get&Transform and saves with Load To - Worksheet. This excel file is 6.136 KB. It has 15 sheets loaded with data. I load the same bunch of tables (15) with Get&Transform and saves with Load To Connection only & Add to data model. This file is 11.995 KB. Hmm - that was a surprise! The columnar database version is twice the size of the version with all tables loaded into sheets. Am I missing something in my anticipation that it should be the opposite?
@hosseinhosseinpoor9561
@hosseinhosseinpoor9561 2 года назад
top
@henrytran4782
@henrytran4782 4 года назад
I attempted to do this with Excel file format and an error is "OLE DB or ODBC error: [Expression.Error] The key didn't match any rows in the table.." Anyone else experienced the same error and how to fix it?
@JedThibodeau
@JedThibodeau 3 года назад
Hello, I'm having an issue where the Data Model is using too much RAM and causing excel to crash. The data is about 18,000 rows, nowhere close to the 3 million rows of data demonstrated in this video. The data is loaded as connection only from about 15 web queries, transformed, then appended to one new query. The appended query is then loaded to Power Pivot. There's one lookup table also loaded to Power Pivot and merged (one-to-many) with the appended query. Loading a 2nd query to Power Pivot causes the Power Pivot to use all the memory and crash. Is there any way to prevent Power Pivot from using too much RAM?
@sushantjoshi5865
@sushantjoshi5865 5 лет назад
Hats off to you boss.. U are really empowering us through your videos.. Thanks for that.. had one query on this.. In normal pivot, we can go to base source data anytime. In such a huge database, is there any option to see my base data as single file..? I mean, if I delete my original text file n if I have only power pivot excel, can I create a new text file with all original data??
@excelisfun
@excelisfun 5 лет назад
Not really. There is no one place to see all the 3 millions rows. Would you really want to look at all 3 million rows? You can extract some of the data to a sheet using Existing Connections and DAX (we will see how to do that later in this class), but if you want to manually look at the data, it is better to import text files individually to the sheet. I do not know how to take the Data Model Columnar Database data and make a single Text File. If you every find a way, I would LOVE to know how - because I, like you, have wanted to do exactly that in the past.
@excelisfun
@excelisfun 5 лет назад
Thanks for your support, Sushant, with your comments, Thumbs Ups and Sub : )
@sushantjoshi5865
@sushantjoshi5865 5 лет назад
Thanks for the quick revert..
@excelisfun
@excelisfun 5 лет назад
: )
@sushantjoshi5865
@sushantjoshi5865 5 лет назад
Sorry to bother you again.. But I tried this on my laptop n got 2 more questions.. 1. can I use query in one excel file as it is in another/new excel file? 2. I have a power query in one excel file. I want to keep the formatting steps as it is, but just want to change the folder, from where files are referred by this query. Can I do that? In anticipation of your guidance.. which will help me to improve in power query area..
@Shib_ghosh
@Shib_ghosh 5 лет назад
Thank you so much Mike for this amazing tutorial. I am looking for a dax function measure for powerpivot report which would return the value based on a lookup value from different table. For Example,I have 2 tables as follows City Name State_Code Mumbai MH Pune MH Chennai TM Noida UP Product_Name City Price Computer Mumbai 100000 Computer Noida 70000 Computer Chennai 80000 Computer Pune 60000 The logic for the function i am looking for would be if city name pertains to State_Code = MH then Tax Rate Would be 20%, if city name pertains to State_Code = TM then Tax Rate Would be 18% if city name pertains to State_Code = UP then Tax Rate Would be 15% I dont want a calculated column, i want to create a dax measure for tax value. Thank you so much. Best Regards Shib
@excelisfun
@excelisfun 5 лет назад
Try this video about DAX Lookup: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JHh7BtRugL8.html
@CoryHuber
@CoryHuber 5 лет назад
Do you do any 1-on-1 tutoring?
@excelisfun
@excelisfun 5 лет назад
At this time I do not, because I have an insane backlog of videos to make...
@DIGITAL_COOKING
@DIGITAL_COOKING 5 лет назад
good video mike!!, I like a lot this series just one problem can't understand video number 09 even I watched twice I feel M code hard for me (hard than DAX )
@excelisfun
@excelisfun 5 лет назад
Yes, M Code takes a while to learn. M Code is so much different than the normal Excel and even most of the DAX functions! But just like we humans have been doing for 10,000 years when we need to learn something new, practice, practice, practice!!!! Thanks for your support, DIGITAL COOKING!!!!
@DIGITAL_COOKING
@DIGITAL_COOKING 5 лет назад
thanks for the advice look at this mike: I found this exercise in excel book ""Given any date, find a way to have Excel compute the first day of the month."" and he gives this solution" VALUE(D4&"/"&1&"/"&E4)" (D4: is MONTH function for given date, and E4: is YEARfunction for the same date I did this: EOMONTH(L5;-1)+1 /(L5: given date) I think it's better !!!, even if no thanks for what We learned it with you
@excelisfun
@excelisfun 5 лет назад
MUCH better!!!! Awesome work, DIGITAL COOKING!!!!
@DIGITAL_COOKING
@DIGITAL_COOKING 5 лет назад
thanks, teacher
@hiteshjoshi3148
@hiteshjoshi3148 3 года назад
Sir how to get power pivot I have office home and student 2016 and today I took a monthly subscription of Microsoft office 365 family but couldn't find power pivot option please help
@excelisfun
@excelisfun 3 года назад
You can not get it in student 2016. It is not possible. You must switch to Microsoft 365.
@hiteshjoshi3148
@hiteshjoshi3148 3 года назад
@@excelisfun but I have taken the subscription of office 365 family but I can't find the option of power pivot in com add ins do I have to purchase the office enterprise or Microsoft office 2016 professional plus or Microsoft professional 2016 or else
@excelisfun
@excelisfun 3 года назад
@@hiteshjoshi3148 Do not buy the 2016. Buy Microsoft 365. If you have trouble, phone Microsoft and insist that they get you the correct version that has Power Pivot. Microsoft 365 has it.
@vturn1963
@vturn1963 5 лет назад
Hi Mike. I can't find the link for the homework zipped text files.
@excelisfun
@excelisfun 5 лет назад
I am sorry about that. I am glad that you posted a comment, because I forgot to post the zipped folder. I just posted the zipped folder. You can now have fun with the practice problem : ) P.S. Thanks for letting me know that a file was missing - this helps our Team!!!!!!
@vturn1963
@vturn1963 5 лет назад
ExcelIsFun thanks Mike. The 14 videos have been great by the way.
@vturn1963
@vturn1963 5 лет назад
ExcelIsFun thanks Mike. The 14 videos have been great by the way.
@excelisfun
@excelisfun 5 лет назад
You are welcome! There are many more to come in this series over the next 9 months. Thank you for your support on each video with those comments, Thumbs Ups and Sub : )
@mitchpalmer5998
@mitchpalmer5998 5 лет назад
@@excelisfun I think we have two links for 014MSPTDA TextData and none to 014-MSPTDA-HomeworkTextFiles
@nareshbisht6361
@nareshbisht6361 5 лет назад
Hi Sir is it possible to use countif and max formula together. X. A. 50 Y. B. 29 X. A. 30 Y. A. 32 X. B. 40 Y. B. 55 X. A. 65 Y. A. 76 Find the X with A with Max number.
@excelisfun
@excelisfun 5 лет назад
Formula like this might work: =MAXIFS(C1:C8,A1:A8,"X.",B1:B8,"A.") or =AGGREGATE(14,6,C1:C8/((A1:A8="X.")*(B1:B8="A.")),1) or =MAX(IF(A1:A8="X.",IF(B1:B8="A.",C1:C8)))
@nareshbisht6361
@nareshbisht6361 5 лет назад
@@excelisfun Thank you so much for help me Sir☺️
@excelisfun
@excelisfun 5 лет назад
Which one did you use?
@nareshbisht6361
@nareshbisht6361 5 лет назад
@@excelisfun only mid one is working (Aggregate) any other easy way to do it sir ???
@excelisfun
@excelisfun 5 лет назад
Not that i know with a formula. Those are the formulas to do it.
@henrytnh1
@henrytnh1 4 года назад
Thank you
@excelisfun
@excelisfun 4 года назад
Glad it helps, Henry! #15 in this class will be even better : )
Далее
🎙ПЕСНИ ВЖИВУЮ от КВАШЕНОЙ🌇
3:16:26
Power Query - Avoid "Helper Queries" (+10 Cool Tricks)
18:40
5 Excel Secrets You'll Be Embarrassed You Didn't Know
17:32
🎙ПЕСНИ ВЖИВУЮ от КВАШЕНОЙ🌇
3:16:26