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!
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!
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!
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 : )
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 !!
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?
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.
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?
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.
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.
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...
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 : )
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.
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!
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?
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
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 : )
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 : )
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!!
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 :-)
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.
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.
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 ^^
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.
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
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!
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??
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.
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..
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 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
@@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.
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
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!!
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.
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?
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.
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.!!!!
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.
¡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 !!!!!
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
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?
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]
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.
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!
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!
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!!!!!!
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 : )
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 )
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!!!!
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
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.
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)))
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!
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!
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!
he has donate button, I think I will donate at least a coffee we owe him, right?
Great explanation of columnar database! Thank you Mike.
You are welcome, Leila!!!!
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.
Glad to help you re-discover the awesomeness of this Columnar Database!!!! Thank you for your support, Sandip!!!!
Very well explained ❤️..thanks a lot🙏
#14 MSPTDA and onto #15, an epic Power Pivot video : )
Thanks Mike. Clear and cleaver as allways, it really reinforce my foundations on Power Pivot.
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 : )
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!
Wonderful intro to PowerPivot. Thanks a lot.
Thanks for demystifying the Columnar Database and its many synonyms in this instructive video. I look forward to your videos!
You are welcome, Rob! Many more to come : ) Thanks for your support !
Simple explanation yet covered in-depth complicated concepts. Thanks a lot. It was amazing.
Glad you like it!
Another very well explained and dynamic video, plus an excellent series. Thank you Mike!
You are welcome for the dynamic video. The pdf notes are pretty good for this video too : ) Thanks for your support, Luciano!!!!!
Much much appreciated..great work God bless you keep good work.
Glad the content that I post helps : )
You are the best teacher, it is pleasure listening to you!
Hi, I am learning from you since 6 years.... Salute to you....
Thank you : )
This is the only Channel using an easy-to-learn-language: "the Columnar "Database" thanks Mike!
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!
As always Mike, great easy to understand explanations.
Thanks for sharing these brilliant new techniques
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 : )
This makes way more sense today than when I first watched this in 2018.
That is good. and the tools are much more popular now too : )
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.
You are welcome! Excel is fun, mohamed : )
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 !!
You are welcome, HIMANSHU!!!! It will keep getting more fun as we go on!
Now learning power edition of excel....
Yes!!!! : ) I am glad to help you : )
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?
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.
Thanks Mike. That was informative and helpful. Learning a lot watching your videos.
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?
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.
Thanks Mike. Looking forward to some DAX :) :)
Me too!!!!!! Thanks for your support, John : )
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.
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...
Thanks for noticing the story boarding - it is fun to make : ) Thanks for your support, Geert!!!!
Story boarding... something else to learn!
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 : )
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.
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!
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?
good job very useful. i'm learning PowerBi but this translate better with my background in excel.
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
I come for the information. Stay for the knowledge. Leave with the understanding. Excellent as always Mike!!
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 : )
great video Mike
Glad you like it and it has use, Douglas! Thank you for your support with your comment, Thumbs Up and Sub : )
You r a great teacher, and I find your lessons to be clear, concise and very engaging. Thank you very much.
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 : )
thank you Mr. ExcellsFun you are an incredible talent
Thank you again! Especially for not having ads interrupt. These videos are so valuable.
Hats off! To you. What a video. Amazing.
Thanks
No problem : )
amazing!
Glad it is amazing, Tom : )
Hey man! Your videos are really awesome. Thank you very much!!!!
Congratulations from Brazil!!!
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 : )
Amazing video as always. Thanks Mike for another EXCELlent video.
You are welcome, Syed!!!!
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!!
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.
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 :-)
Thanks for the billions of thanks and thumbs up, Mohamed!!!!!!
Thanks for you great videos.
Great video! Thanks!
You are welcome, Teammate!!!
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.
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.
Thanks:)
You are welcome, pittedmetal!!!!
thank you so much
you are welcome so much!!!
Power Pivot is amazing just like your videos. Thanks again!
You are welcome, again, knikl!!!
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 ^^
Without you, I am Zero in Excel.
Thanks Mike.....You are certainly the best -- at explaining EXCEL and as a HUMAN BEING
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.
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
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!
Thanks!
You are welcome! Thank you for your kind donation. It helps me to keep making free Excel and Power BI Education for the World!
Thank you Mike :) Excited for the next video.
Yes, the next video is epic : )
Power Query & Power Pivot = awesome, great video as ever Mike. Regards
Glad the video is great for you, Hoe To Excel At Excel.Com!!!! Thanks for the support : )
Thanks, Mike. Great introduction to PowerPivot
You are welcome, K B !!!!! Thanks for your support : )
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??
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.
Thanks for your support, Sushant, with your comments, Thumbs Ups and Sub : )
Thanks for the quick revert..
: )
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..
Power Pivot rocks. Great video Mike, as always!
It does rock!!!! Thanks for the support, Chris : )
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
You can not get it in student 2016. It is not possible. You must switch to Microsoft 365.
@@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
@@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.
Mike G. You are the Excel Institute to me!
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 : )
You have tremendous value in my knowledge and my output
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
Try this video about DAX Lookup: th-cam.com/video/JHh7BtRugL8/w-d-xo.html
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!!
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.
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?
Not that I know of. Glad the video helps, and thanks for your help and support with comment, Thumbs Up and your Sub : )
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.
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.!!!!
Thanks for great explanation of columnar database!
You are welcome, Malina!!!!! Thanks for your support : )
It's absolutely awesome! Thanks a lot!
Excellent explanation. Thanks Mike
top
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!
Boomeranging back is good : )
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.
hahahaha... It is magic!!!!!!!! Excel and Power Query and Power Pivot and Data Model and Columnar Database and Power BI Magic!!!!!!
Eres un máster, gracias por tu pasión x Excel. Saludos desde Chile from end the World.
¡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 !!!!!
Great video thank you
You are welcome!!!!
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
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?
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]
What is the [Avg Cost] Measure?
This is iterating over Fact table: FILTER ( fSales, fSales[Failure Date]
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.
Do you do any 1-on-1 tutoring?
At this time I do not, because I have an insane backlog of videos to make...
Thank you, Mike!
Great explanations as always!
You are welcome!
Another very well and clearly explained video, thanks!
Glad it is clear for you, Armond!!!!
Absolutely masterclass
Glad you like the masterclass, Naveed!!! Thanks for the support with your comment, thumbs up and of course your Sub : )
another excellent video
Glad you like the Columnar fun, Steven! Thanks for the support : )
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!
I have not tried the feature yet, what does it do?
When in PowerQuery edit mode it shows column statistics such as %error or %empty rows and number of unique or distinct values per column.
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!
Great video, as usual, Mike! Thanks so much!
You are welcome, Jack!!! Thank you for your support : )
Brillant as Usual!
Glad you like it, Mahesh!!!! Thank you for supporting my efforts with your comments and thumbs ups, and of course your Sub : )
Hi Mike. I can't find the link for the homework zipped text files.
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!!!!!!
ExcelIsFun thanks Mike. The 14 videos have been great by the way.
ExcelIsFun thanks Mike. The 14 videos have been great by the way.
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 : )
@@excelisfun I think we have two links for 014MSPTDA TextData and none to 014-MSPTDA-HomeworkTextFiles
Thank You Sir
You are welcome, M. A.!!!!
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 )
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!!!!
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
MUCH better!!!! Awesome work, DIGITAL COOKING!!!!
thanks, teacher
Thanks a lot!
You are welcome a lot, Rodrigo!!!! Thanks for the support for the excelisfun channel with your comments, Thumbs Ups and your Sub : )
Thanks Mike for another great video.
You are welcome, Dave - Thanks for your support : )
Thank you
You are welcome!
Thank you
Glad it helps, Henry! #15 in this class will be even better : )
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.
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)))
@@excelisfun Thank you so much for help me Sir☺️
Which one did you use?
@@excelisfun only mid one is working (Aggregate) any other easy way to do it sir ???
Not that i know with a formula. Those are the formulas to do it.
Can’t wait for part 3
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!
Your prep and setup examples to each video are amazing and something to learn from also...thank you!
Your prep and setup examples to each video are amazing and something to learn from also...thank you!
You are welcome!
Amazing
Glad it is amazing for you, Thanks for the support!!!! : )