I would like to tell you that I am one of the viewers of your Excel Basics Series. I have just recently completed the series and feel that they are simply amazing. Yesterday my dad was working on some excel files and I saw that he was working way too hard for the purpose! I got him exactly what he wanted through pivot tables. Your videos have helped me a lot. He was so happy to see how much simpler his work had gotten and also gave me a few other excel sheets to fix! Thank you so much, Mike. I can't thank you enough. I jist can't. Thank you so so much! I hope you get the best in life!
That is why i have posted over 3000 videos over for over 10 years : ) So people in the world can use Excel to have fun, be efficient and helps others to do cool stuff with excel!!!! AND, thank you, Yashvardhan, for so consistently supporting this channel : ) Every comment you leave is really great : )
ExcelIsFun You, sir, deserve the very best. Thank you so much! It is the person's pleasant qualities that encourage others to compliment and thank them. You definitely have those qualitites.
This is a great great video. It's not only illustrate how to use Power Query but also how to build an effective data model in Power BI. There are lots of tips and tricks. Other youtuber will show only how to use commands, but you make people thoroughly understand concept(s) and commands. I love it very much. Many thanks, Mike.
Yes, I am happy to teach the concepts and tricks. That is what i have been doing for over 10 years here at TH-cam : ) I am glad that you love it - because I love to make these videos. You can help support this free resource that I post by commenting when you are inspired, clicking Thumbs Up on each video, Subscribing and tell all your friends : ) BTW, if you like this video, you will LOVE the new class i am posting over the next nine months: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html Thanks for your support, Nattawut : )
Thanks a lot, Mike. I'm a big fan of you and have followed your channel more than 3 years. I have learnt tons of techniques. I can feel that you love to make these videos and it makes all video lively. Your noise, your emotion, mouse movement can express that. You are my great teacher. Big thanks from Thailand. P.S. I will follow the MSPTDA series for sure
I am glad it all helps! And, yes, if you love what you do - good things will happen : ) And be sure to help and support with those Thumbs Up and comments : ) Thanks, Nattawut!!!
It's taking me a while to wrap my head around Star Schema data models and which fields should be in the fact table versus dimension tables, but I'm getting there. Thanks for another great video.
This transform to the Star Schema Data Model is the max! Perfect exercise to define and split the use of PQ vs. DAX. This is my new preferred method. As you can see I need to re-baseline once again (although I would probably stay in Excel/PP, for now). If this method were included in the competition -- it would get my single vote. This mini series on how to solve this problem in various ways keeps on giving, and our understanding of relational data bases keeps on growing -- as ordinary Excel users we need this kind of exercises in order to expand from Excel into this new field. Thank you Master Mike for your Due Diligence and perseverance! We collectively salute you! :-)
Yes, it is amazing what Microsoft has done over the last 10 years with dynamic Excel Tables, Power Query and Power Pivot so that we can do soooooo much more in Excel. We can even store 100 milliow rows in Excel's Power Pivot Data Model : ) You are welcome for the videos, Sara!!!
Mike, Power Query is most helpful when it can make Power Pivot easier, which is why this video is the best practice among solutions using Power tools in the playlist. But PivotTable is the easiest solution of all.
Yes, as time goes on and I use Power Query more and more, Power Query is the tool that makes everything easier!! Thanks for stopping by in the comments, Joad Lee : )
That’s awesome! As usual. You’ve asked how to hide fields in report view. I usually create a separate blank table only with measures so I can easy hide whole fact table.
That's what I usually do, too. Not going to lie though, really liking the idea of putting the measures in the fact table and hiding the other values. More effort, but probably an easier experience for the end user.
Thank you Mike, the "Group By" strategy is something I'll have to try (I was trying pivoting, removing duplicates, and Cartesian Joins and it wasn't working for what I was working on... I think my way might work but it's too messy).
You are welcome, Jung!!! Thanks you for the support with your comments, Thumbs Ups and Sub to help me keep going : ) P.S. Many more Power Query videos to come over the next year. Also, do you know about the over 100 Power Query videos that I have already posted over the last 3 years? Here is the playlist: th-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html
Yes, I am sorry about that. I have edited the description so that it is there. I just wish someone had mention this sooner. Thanks, kumail, for your support of the excelisfun channel : )
Fantastic video Mike. I have learned a great deal from your tutorials of late. Any thoughts of a video to show how to manage the numerous data source files (i.e exports from accounting systems, folder location mgmt to Access db's etc..?) Managing these multiple source connections efficiently takes some thought and organization. Keep up the great work...Thanks
I have videos on importing from folders, Access, Excel, SQL database and multiple locations. Here is a playlist of Power Query Videos: th-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html This video playlist has over 100 videos, but most of the topics are covered in a video in this list. However, I do not have a single video that goes over thought and organization of all the connections for one final model and reporting system. What are some of the issues that you face?
Thanks Mike. I will be sure to go through the playlist. My thoughts are geared towards "Best Practices" for organizing the exported files from my accounting software into a folder, then running them through power query to import them into my Access db. With more of your awesome content, I am sure I will learn the proper way. Keep up the fantastic work!!
Fantastic video, Mike! Thank you! Question on this subject: what do I do in a situation when the intermediate table (one side of the relationship - say, fPatientVisits) has more records (PK) records than the same (FK) records the child table has fPatientVisitProcedures (many side of the relationship on PatientVisitID). In other words, not every Visit has a Procedure. If I join based on PK from fPatientVisits, will I have to do a LEFT JOIN on fPatientVisits and generate additional empty values in the resulting fPatientVisitProcedurestable? Basically, I don't want to count my number of unique visits incorrectly in the resulting table only because not every visit had a procedure. Many thanks!
How do we handle the case of changing dimensions? For example, employee sales performance as fact, and employee as dimension. Previously, the employee is assigned to sales team A. Now, the employee is assigned to sales team B. If we want to analyze performance for various teams over time, how should the schema be built to accommodate this kind of situations?
I made 5 videos about your question. Did you see them? I never saw your comments below the videos I posted, so I was not sure if you knew that I made videos for you. You should have seen the announcements if you were subscribed. Are you subbed? Here is the playlist of videos: th-cam.com/play/PLrRPvpgDmw0nvWXu8EwGQnfoJv7Ca2st3.html
I have a question. On the total shipping field. You did a divide and then after you multiplied. I usually put my divide in parentheses. So in M language it doesn’t matter it will know to apply the divider before the multiplier?
It does not matter what system in the history of the world that you use, Math Order of Operations rules supreme!!!! Since, left to right, we divide first to get % allocation rate, then multiple, you do not need the Parenthesis in Excel, Power Query, DAX or whatever : )
Is there ever an instance where you would use multiple fact tables? For example: would you combine Sales, Product Costs, and Payroll fact tables? What if there were several fields in the payroll table that aren't relevant to sales records?
Thanks so much for you video. Pls my question may not relate to the current video and i am sorry for this. i want to ask if you have any video using power query for approximate match for dates.
Hi Mike. I am trying to create a MonthName column from MonthNumber using Format([MonthNumber],"MMM"). The problem is, it is converting the number 1 to January and remaining every number (2 to 12) to December or number 1 to December and remaining month numbers to January. I don't know if there is BUG or my version is not working properly. Can you please help me with this problem? Help is much appriciated.
why dont you remove weight field from dproduct table.. because this is already calculated in fact table ? and in dimension we just want fields not any numeric data ?
I would like to tell you that I am one of the viewers of your Excel Basics Series. I have just recently completed the series and feel that they are simply amazing. Yesterday my dad was working on some excel files and I saw that he was working way too hard for the purpose! I got him exactly what he wanted through pivot tables. Your videos have helped me a lot. He was so happy to see how much simpler his work had gotten and also gave me a few other excel sheets to fix! Thank you so much, Mike. I can't thank you enough. I jist can't. Thank you so so much! I hope you get the best in life!
That is why i have posted over 3000 videos over for over 10 years : ) So people in the world can use Excel to have fun, be efficient and helps others to do cool stuff with excel!!!! AND, thank you, Yashvardhan, for so consistently supporting this channel : ) Every comment you leave is really great : )
ExcelIsFun You, sir, deserve the very best. Thank you so much! It is the person's pleasant qualities that encourage others to compliment and thank them. You definitely have those qualitites.
This is a great great video. It's not only illustrate how to use Power Query but also how to build an effective data model in Power BI. There are lots of tips and tricks. Other youtuber will show only how to use commands, but you make people thoroughly understand concept(s) and commands. I love it very much.
Many thanks, Mike.
Yes, I am happy to teach the concepts and tricks. That is what i have been doing for over 10 years here at TH-cam : ) I am glad that you love it - because I love to make these videos. You can help support this free resource that I post by commenting when you are inspired, clicking Thumbs Up on each video, Subscribing and tell all your friends : ) BTW, if you like this video, you will LOVE the new class i am posting over the next nine months: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
Thanks for your support, Nattawut : )
Thanks a lot, Mike. I'm a big fan of you and have followed your channel more than 3 years. I have learnt tons of techniques. I can feel that you love to make these videos and it makes all video lively. Your noise, your emotion, mouse movement can express that. You are my great teacher.
Big thanks from Thailand.
P.S. I will follow the MSPTDA series for sure
I am glad it all helps! And, yes, if you love what you do - good things will happen : ) And be sure to help and support with those Thumbs Up and comments : ) Thanks, Nattawut!!!
Thank you Sir for the great video! I was trying to denormalize 2 fact tables using SQL, but your approach is much simpler and effective!
Power Query really can make things simple! You are welcome for this : )
A for apple... E for ExcelIsMike... M for MikeIsExcel... Thats the foundation line of this channel
AEM!!!!
I can't describe your smartness in excel anymore you are beyond perfect
Just a guy having fun with Excel and trying to spread the fun around the world : ) Thanks for your consistent support, Ismail!!
It's taking me a while to wrap my head around Star Schema data models and which fields should be in the fact table versus dimension tables, but I'm getting there. Thanks for another great video.
You are welcome!
Never seem to have enough of this video and practice!!!! This is just Amazing.
Thanks! Reviewed this again, and it’ll help me allocate and round properly in work project. Have a coffee or beer on me.
Thanks for the coffee and beer, Milhouse!!!!
POWER QUERY - Explain in Dept thanks for this explanation sir.
You are welcome, Teammate!
This transform to the Star Schema Data Model is the max!
Perfect exercise to define and split the use of PQ vs. DAX. This is my new preferred method.
As you can see I need to re-baseline once again (although I would probably stay in Excel/PP, for now).
If this method were included in the competition -- it would get my single vote.
This mini series on how to solve this problem in various ways keeps on giving, and our understanding of relational data bases keeps on growing -- as ordinary Excel users we need this kind of exercises in order to expand from Excel into this new field.
Thank you Master Mike for your Due Diligence and perseverance!
We collectively salute you! :-)
I've always used Access to build databases, but with this power table addition I could try and build a Employee database. Thank you Sir Mike.
Yes, it is amazing what Microsoft has done over the last 10 years with dynamic Excel Tables, Power Query and Power Pivot so that we can do soooooo much more in Excel. We can even store 100 milliow rows in Excel's Power Pivot Data Model : ) You are welcome for the videos, Sara!!!
Man! This is intense! Thanks for helping me see what a star schema is.
Intense is good : ) You are welcome, Oz!!!
Superb Video! This helped me to solve the problem I had been struggling with for days. Thank you so much! I learned so much
Dear, thank for you by size sky ,these video very great work ,your channel help me mor to develop my work .I wish u happy life .
Thank you for the Power Query fun!
You are welcome, Teammate! : )
Mike, Power Query is most helpful when it can make Power Pivot easier, which is why this video is the best practice among solutions using Power tools in the playlist. But PivotTable is the easiest solution of all.
Yes, as time goes on and I use Power Query more and more, Power Query is the tool that makes everything easier!! Thanks for stopping by in the comments, Joad Lee : )
That’s awesome! As usual.
You’ve asked how to hide fields in report view. I usually create a separate blank table only with measures so I can easy hide whole fact table.
Got it! Thanks for the tip!
That's what I usually do, too. Not going to lie though, really liking the idea of putting the measures in the fact table and hiding the other values. More effort, but probably an easier experience for the end user.
Thank you Mike, the "Group By" strategy is something I'll have to try (I was trying pivoting, removing duplicates, and Cartesian Joins and it wasn't working for what I was working on... I think my way might work but it's too messy).
Thank you Mike for Power Query video. I'm very interested in Power Query technique to solve problem.
You are welcome, Jung!!! Thanks you for the support with your comments, Thumbs Ups and Sub to help me keep going : ) P.S. Many more Power Query videos to come over the next year. Also, do you know about the over 100 Power Query videos that I have already posted over the last 3 years? Here is the playlist: th-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html
Thanks Mike for this EXCELlent video. Always perfect
Thanks, Syed!!! Thanks as always for your support : )
Amazing Video Sir, suggest you should create Key/Core Measure Table separately for measures only
you can use constellation schema if you have multiple fact tables
This is great!! It seems, I couldn't find referred excel data file in comments to test it.
Yes, I am sorry about that. I have edited the description so that it is there. I just wish someone had mention this sooner. Thanks, kumail, for your support of the excelisfun channel : )
Thanks.
Amazing, learnt great new things
Glad the videos help : )
Thanks you! This was fireworks! :)
Glad you like it, Mueez!!!
Star Schema waw very fun Excel makes life easy
Thanks for Power BI fun :)
Simply amazing MIke!!!!
Glad you like it, Chris! Thanks for the support : )
As always, great video. Do you also have other channels for teaching other office products? Thanks.
No. Sorry. But I do have a playlist with some basic office tools: th-cam.com/play/PLrRPvpgDmw0l45snFj8uDmuDUMQeBAVtd.html
Thanks for your support with your comments, Thumbs Ups and Sub : )
Thanks for the reply. And I'm a subscriber already. How could I not be? Great Channel.
Fantastic video Mike. I have learned a great deal from your tutorials of late. Any thoughts of a video to show how to manage the numerous data source files (i.e exports from accounting systems, folder location mgmt to Access db's etc..?) Managing these multiple source connections efficiently takes some thought and organization. Keep up the great work...Thanks
I have videos on importing from folders, Access, Excel, SQL database and multiple locations. Here is a playlist of Power Query Videos: th-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html This video playlist has over 100 videos, but most of the topics are covered in a video in this list. However, I do not have a single video that goes over thought and organization of all the connections for one final model and reporting system. What are some of the issues that you face?
Thanks for your support with your comments, Thumbs Up and Sub, Matt : )
Thanks Mike. I will be sure to go through the playlist. My thoughts are geared towards "Best Practices" for organizing the exported files from my accounting software into a folder, then running them through power query to import them into my Access db. With more of your awesome content, I am sure I will learn the proper way. Keep up the fantastic work!!
Fantastic video, Mike! Thank you! Question on this subject: what do I do in a situation when the intermediate table (one side of the relationship - say, fPatientVisits) has more records (PK) records than the same (FK) records the child table has fPatientVisitProcedures (many side of the relationship on PatientVisitID). In other words, not every Visit has a Procedure. If I join based on PK from fPatientVisits, will I have to do a LEFT JOIN on fPatientVisits and generate additional empty values in the resulting fPatientVisitProcedurestable? Basically, I don't want to count my number of unique visits incorrectly in the resulting table only because not every visit had a procedure. Many thanks!
How do we handle the case of changing dimensions? For example, employee sales performance as fact, and employee as dimension. Previously, the employee is assigned to sales team A. Now, the employee is assigned to sales team B. If we want to analyze performance for various teams over time, how should the schema be built to accommodate this kind of situations?
I made 5 videos about your question. Did you see them? I never saw your comments below the videos I posted, so I was not sure if you knew that I made videos for you. You should have seen the announcements if you were subscribed. Are you subbed? Here is the playlist of videos: th-cam.com/play/PLrRPvpgDmw0nvWXu8EwGQnfoJv7Ca2st3.html
Thanks Mike :) :)
You are welcome, John!!! Now that you have downloaded Power BI Desktop, you have even more Microsoft power and ability to have fun : )
I have a question. On the total shipping field. You did a divide and then after you multiplied. I usually put my divide in parentheses. So in M language it doesn’t matter it will know to apply the divider before the multiplier?
It does not matter what system in the history of the world that you use, Math Order of Operations rules supreme!!!! Since, left to right, we divide first to get % allocation rate, then multiple, you do not need the Parenthesis in Excel, Power Query, DAX or whatever : )
Thanks for your support, Karl : )
ExcelIsFun good to know one less step to do :)
Is there ever an instance where you would use multiple fact tables? For example: would you combine Sales, Product Costs, and Payroll fact tables? What if there were several fields in the payroll table that aren't relevant to sales records?
This is a very good question! I hope someone will answer it ... sometime
Thank You.....
:)
You are welcome!
Thanks so much for you video. Pls my question may not relate to the current video and i am sorry for this. i want to ask if you have any video using power query for approximate match for dates.
I do not have a video on that... Maybe Oz video: th-cam.com/video/EYgKciBr_dg/w-d-xo.html
Hi Mike. I am trying to create a MonthName column from MonthNumber using Format([MonthNumber],"MMM"). The problem is, it is converting the number 1 to January and remaining every number (2 to 12) to December or number 1 to December and remaining month numbers to January. I don't know if there is BUG or my version is not working properly. Can you please help me with this problem? Help is much appriciated.
You have to do it on the [Date] column, not the [MonthNumberColumn].
It's the same issue even I use the Date Column. I tried but it is still the same.
why dont you remove weight field from dproduct table.. because this is already calculated in fact table ? and in dimension we just want fields not any numeric data ?