MSPTDA 21: Power Query: Reduce Data Model Size, Transformations to Columnar Database Size

Подписаться 1 млн

Просмотров 32 тыс.

50% 1

All 4 files used in video and homework are available in this zipped folder: people.highline.edu/mgirvin/A...
Download file individually at class web site: people.highline.edu/mgirvin/A...
pfd notes for Video #21: people.highline.edu/mgirvin/A...
In this video learn key strategies and Power Query Transformation Methods for reducing the size of your Data Model, or Columnar Database or File size. This is an important part of Data Analysis and Business Intelligence when you have Big Data because it can help your solution to run more efficiently by creating smaller Data Model behind the scenes Columnar Databases that must be loaded into RAM. Apply concepts of how the Columnar Database stores data when deciding what tables, columns and transformations should be part of the final Data Model.
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
2. (01:11) Download HUGE CSV File.
3. (01:34) Talk about How Columnar Database works and how we can reduce size.
4. (03:11) Rules for Efficient Data Modeling
5. (06:07) Get Data From Government Web Site: data.seattle.gov/Public-Safet...
6. (07:18) Import CSV Text File
7. (07:46) Load Entire CSV File and look at Model Size.
8. (09:20) Filter Rows and look at Model Size.
9. (10:56) Remove Columns and look at Model Size.
10. (11:40) Reduce Cardinality of Columns with 1) Add Column for Time
11. (13:59) Reduce Cardinality of Columns with 2) Transform Columns and get Date Only
12. (15:21) Edit Time Column to Create only Hour and reduce size further.
13. (16:39) Edit Query can lead to trouble in subsequent steps. See how to fix the problem.
14. (17:28) Add Date Table
15. (18:28) Create Relationship
16. (18:44) Create Measure to Count Rows for Call Type. Use COUNTROWS DAX Function.
17. (19:16) Create Dashboard
18. (19:16) Create Pivot Table
19. (20:17) Filter to show Top 20, 911 Call Types
20. (20:45) Show Values As in Data Model PivotTable
21. (22:07) Sort Measure Biggest to Smallest
22. (22:15) Add Slicers
23. (23:05) Scrape Data form Web Site
24. (24:18) Summary

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

1 авг 2024

Ссылка:

Скачать:

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

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 141

@AdiCristea 5 лет назад

The DateTime filter may have left out some values of importance as we do not include anything for the last day in 2018 due to the fact we conditioned the column to be less or equal than 12/31/2018 00:00:00, which means everything between 12/31/2018 00:00:01 and 12/31/2018 23:59:59 will not be there, am I right?

@excelisfun 5 лет назад

Yes, you are correct! Thanks for that catch and edit, Adi!!! Here is what it should have been: = Table.SelectRows(#"Changed Type", each [Original Time Queued] >= #datetime(2017, 1, 1, 0, 0, 0) and [Original Time Queued] < #datetime(2019, 1, 1, 0, 0, 0))

@excelisfun 5 лет назад

I have pinned your keen insight to the top so other Teammates will benefit, Adi : )

@GeertDelmulle 5 лет назад

mmm... always look at the boundaries, that's where things can go wrong. Good catch!

@sherryizzie5309 5 лет назад

I have watched MSPTDA series for a few times . Every time I can pick up something new from each video lesson. Wonderful work on this series!! Love it!

@gregfollett8285 Год назад

Great Stuff Mike! I can put this lesson to work immediately in my job. I am working with files of over 2 million rows and now I know how to create data models that run more efficiently. Thank you so much for sharing your expertise. The things I am learning from your channel make me look like a genius at work, and people do notice.

@excelisfun Год назад

That has my goal for 14 years at RU-vid: Free Excel and Power BI Education for the World!!!! I am so glad that I can help you, Greg!!

@GeertDelmulle 5 лет назад

A super efficient method and result -- wouldn't expect anything less (or should I say: "more") from you, Mike! :-) Thanks for the lesson.

@excelisfun 5 лет назад

You are welcome, Geert : )

@petermyran4986 5 лет назад

Mike - you are on fire this summer!! And I don't mean from the excessive heat. Another excellently instructive video. Your generosity in sharing your expertise with us borders on the saintly :)

@excelisfun 5 лет назад

Thanks for your kind words! I am happy when I can post quality education for free and when viewers like you comment and support : )

@petermyran4986 5 лет назад

@@excelisfun Mike, I am always so impressed by the very high quality of the instruction you provide. AND, don't think I haven't noticed the many constant improvements & enrichments you are continuously making - taking advantage of improvements of the medium. I of course watch other instruction videos in the tech space, but I learn best and most easily from you - over and over and over again! Again, thank you!!!!!

@excelisfun 5 лет назад

@@petermyran4986 Yes, sir! Thanks for noticing the hard work I have to put in to tell a good story that can instruct, inspire and most importantly convert complicated topics in a way that seems less complicated. Further, topics like DAX and Statistics do not come easy to me, and when I take classes from others I often can not understand what it is that is being taught; then it is not until I break down the individual concepts that are being taught, re-arrange them with diagrams and a presentation that helps me to understand. Then I take that as the basis to try and make these videos. Also, thank you for your consistent support even when it is a plain thank you. It means a lot.

@RobMichaels1 5 лет назад

I especially liked that you showed how reducing cardinality (unique values) in each column reduced the columnar data base and hence the file size. A very useful technique. Thanks for the great video as always!

@excelisfun 5 лет назад

You are welcome, Rob!!!! Thanks for your support : )

@timmybone13 2 года назад

This cleared up so many questions I had about building columnar databases. I thought I had to do that manually - I'd been trying off and on for months with work data. This video showed me Excel does it automatically! Total light bulb moment. Thank you so much for another great video Mike.

@excelisfun 2 года назад

You are welcome!

@a.achirou6547 Год назад

Awesome Use Case of Power Query! This presentation is insightful. Thank you for your valuable time and your passion to share knowledge.

@houstonsam6163 5 лет назад

Dude, you are *the* Rock Star on this stuff. Many thanks for the education you provide to so many of us. Glad I bought your book on array formulas as well.

@excelisfun 5 лет назад

You are welcome, Sam! I am glad it all helps you and many others around the world! Thanks for your support with your comments, Thumbs Ups, Your Sub and for buying the books : ) Many more videos to come!!!

@wayneedmondson1065 5 лет назад

Great stuff Mike.. super cool the way you reduced the file size so dramatically with some thoughtful and insightful transformations. I learned a lot. Thumbs up!

@excelisfun 5 лет назад

Glad you are learning a lot, Wayne! Thanks for your consistent support : )

@johnborg6005 5 лет назад

Thanks Mike. Seeing all the capabilities of all functions in Power Query that are seen in all your videos, I can practiced and adapt in other situations in my work, and that's amazing. However the beauty of it is to learn and practice and not just copy. The #time function.....I didn't know that. A Feast of info to practice and learning. Thanks again. :) :)

@pravinshingadia7337 4 года назад

Great video Mike! Thanks as always for the effort you put in to pass on your vast knowledge to us.

@MalinaC 5 лет назад

712 to 43 - absolutely amazing!!! Thanks for sharing, Mike :)

@excelisfun 5 лет назад

712 to 2, you mean : ) You are welcome, Teammate!!!!!

@Sal_A 5 лет назад

Excellent data modeling tips for reducing the cardinality of the fact and dimension table columns. Thumbs up.

@excelisfun 5 лет назад

Thanks Sal A!!!! Cardinality is fun!!!!

@sanjeevsoni6793 5 лет назад

Size - Reduction is Excellent !! I live the sorting data with the measure the best !! Three cheers1!

@excelisfun 5 лет назад

Did you love the sorting in the PivotTable? Is that what you mean? Thanks for your support on each video, Sanjeev!!!

@joaquimcosta952 4 года назад

Like always.....the one of best knowledge it’s here..

@gentle2005phir 5 лет назад

From google searching data source , breaking apart date time, renaming pivot field , hiding slicer field with no data to using web table , gr8!!!

@excelisfun 5 лет назад

Glad it is great for you, Gentle Raj : ) : )

@Victor-ol1lo 5 лет назад

Great, as always! From 700 to 2 MB within minutes. Not bad... :-) Thumbs Up !

@excelisfun 5 лет назад

Power Query is Fun!!!!! Thanks for your support, Victor!!!

@katerina6495 5 лет назад

Thank you, Mike, great video as always

@excelisfun 5 лет назад

You are welcome, Katerina!!!

@JoshuaDHarvey 4 года назад

Great video, thank you!

@PedroCabraldaCamara 5 лет назад

another amazing video. Thank you so much Mike

@excelisfun 5 лет назад

You are welcome, Pedro!!! Thanks for your support : )

@chrism9037 5 лет назад

This was great, thanks Mike!!!

@excelisfun 5 лет назад

Glad it was great, Chris!!!!

@armondnazarian4455 4 года назад

Great as always Mike!

@excelisfun 4 года назад

Glad it is great for you, Armond! I am happy to see that your are working your way through the MSPTDA class : )

@bojanjovanovic756 3 года назад

Really amazing , many many many thxs

@excelisfun 5 лет назад

Topics: 1. (00:15) Introduction 2. (01:11) Download HUGE CSV File. 3. (01:34) Talk about How Columnar Database works and how we can reduce size. 4. (03:11) Rules for Efficient Data Modeling 5. (06:07) Get Data From Government Web Site: data.seattle.gov/Public-Safet... 6. (07:18) Import CSV Text File 7. (07:46) Load Entire CSV File and look at Model Size. 8. (09:20) Filter Rows and look at Model Size. 9. (10:56) Remove Columns and look at Model Size. 10. (11:40) Reduce Cardinality of Columns with 1) Add Column for Time 11. (13:59) Reduce Cardinality of Columns with 2) Transform Columns and get Date Only 12. (15:21) Edit Time Column to Create only Hour and reduce size further. 13. (16:39) Edit Query can lead to trouble in subsequent steps. See how to fix the problem. 14. (17:28) Add Date Table 15. (18:28) Create Relationship 16. (18:44) Create Measure to Count Rows for Call Type. Use COUNTROWS DAX Function. 17. (19:16) Create Dashboard 18. (19:16) Create Pivot Table 19. (20:17) Filter to show Top 20, 911 Call Types 20. (20:45) Show Values As in Data Model PivotTable 21. (22:07) Sort Measure Biggest to Smallest 22. (22:15) Add Slicers 23. (23:05) Scrape Data form Web Site 24. (24:18) Summary

@MySpreadsheetLab 5 лет назад

Amazing video. Thanks Mike!

@excelisfun 5 лет назад

You are welcome, Teammate Kevin!

@nikiss8 5 лет назад

Thanks Mike really nice. I do all the steps you did daily, great help and further tools to use. However I mostly do reconciliation. Data A v Data B. Most troublesome are --dates--, as there can be delays depending on location and size. Anything date related and rec is great help, especially time delay. One source has date processed, another date received. The same transaction, but it can be delay between 1-7 days. This on its own is not an issue, but further down the line, formulas get beyond me, especially if there are discrepancies every day.

@excelisfun 5 лет назад

Do you have a specific data set, maybe you could make a small sample one? What do you do now/ What specific calculation do you want to make? Maybe I can make a video for this class based on your question. If you have some data and can explain your situation and specifically what you want, maybe I can make a video. E-mail to excelisfun at gmail.

@maniyan0101 3 года назад

You are the best.. 👍

@excelisfun 3 года назад

Always glad to help!!!

@belsergey8096 5 лет назад

The best! As usual

@excelisfun 5 лет назад

Glad you like it, bel!!! Thanks for your support : )

@johnborg6005 5 лет назад

Amazing Video Mike. Thanks

@excelisfun 5 лет назад

You are welcome, John Borg : )

@johnborg6005 5 лет назад

Seeing the video again and again.....Amazing!!!! simply Amazing!!!!

@excelisfun 5 лет назад

@@johnborg6005 Great! What makes it amazing for you, John? Do you have to import lots of CVS files, or big data files, or other?

@johnborg6005 5 лет назад

@@excelisfun I do have CSV Files to import, but the presentation of the video and the explanation is terrific. :)

@majdyazigi8185 5 лет назад

Mike, this is beautifulllllllllllll

@excelisfun 5 лет назад

Glad it is beautiful for you!!!! It is fun too : ) Thanks for your support!

@SyedMuzammilMahasanShahi 5 лет назад

This is awesome. Thanks Mike for the awesomeness :P

@excelisfun 5 лет назад

You are welcome for the awesomeness, Syed!!! Thanks for your amazing and consistent support : )

@nimrodzik1 5 лет назад

Amazing. Thanks Mike :)

@excelisfun 5 лет назад

You are welcome, nimrodzik1!!!!

@drsteele4749 5 лет назад

Wow, Mike! What a marked reduction in size. [Now, if you could just reduce that crime at the same time...…]

@excelisfun 5 лет назад

That is a good one, DRSteele!!!!

@mohamedchakroun4973 5 лет назад

Briliant as always mike :-)

@excelisfun 5 лет назад

Glad you like it, Mohamed!!!!

@davebowman5392 5 лет назад

Thanks Mike!

@excelisfun 5 лет назад

You are welcome, Dave!!!!!!

@SaniGarba 5 лет назад

Thank you!

@excelisfun 5 лет назад

You are welcome, Sani!!!!

@AnbarasuAnnamalai 2 года назад

It is really nice

@excelisfun 2 года назад

Glad it helps!!!

@huynhdinhtuananh 4 года назад

Love it

@excelisfun 4 года назад

Glad you love it, Huynh from Vietnam!!!!! Thanks for your support on each video that you learn from with your comment, thumbs up and of course your Sub : ) It helps me to make even better video!!!!

@jeffkasavan93 4 года назад

Thanks for this informative video, Mike - I notice at 21:36 you typed in the name of a new column. I have noticed that this behavior in Excel pivot tables has existed for a long time. I use this for example to replace all the winery site codes in my organization (6 digit number) with an abbreviation that is meaningful, and this spreads throughout the whole pivot table. It's acting like a Vlookup but without having to create any Vlookup. Excel takes my input as if to say "You are saying that you do not want to see winery 223345 as that number any more but instead you want to see it as VRT. Ok - everywhere that 223345 displays gets changed to VRT." It's kind of data magic, really...

@excelisfun 4 года назад

Magic is good : )

@kennethstephani692 3 года назад

Golden!

@excelisfun 3 года назад

Glad it helps : )

@ismailismaili0071 5 лет назад

thank you so so much Mr. Mike I'm so sorry I'm not watching the videos that doesn't mean i'm not available no i'm just in Ramadan as you know it's a religions month so we give all our time to it. hope you understand and I always appreciate your videos thanks again.

@excelisfun 5 лет назад

I understand and appreciate you, Ismail!!!! You can watch the videos anytime you would like : )

@graemewatkin7834 5 лет назад

You need to add this series to Udemy or some other site, you deserve rewards for the quality content.

@excelisfun 5 лет назад

My goal for 11 years at RU-vid is to NOT post my classes at Udemy and other sites that charge money and instead post them here, so free education for the world is available. Much of what I post is better than the for-pay-classes, and that is with intent! I hope you like the videos and learn from them, Graeme! Rather than pay money, just leave a comment and thumbs up on each video and subscribe : ) That is pay enough.

@graemewatkin7834 5 лет назад

Mike, you are an inspiration to the community and are much too kind. I've been watching your videos for years which has given me a certain set of skills (not Liam Neeson level but still) which I use for work and to help others on forums like MrExcel.

@excelisfun 5 лет назад

@@graemewatkin7834 , Glad to hear that the things I post and inspirational and help you to help others at Mr Excel Forum!!! Who is Liam Neeson? I look forward to your comments and thumbs ups on each video that helps : )

@tigerdataimarticus7088 3 года назад

Very nice 👍

@excelisfun 3 года назад

Glad you like the videos : )

@tigerdataimarticus7088 3 года назад

ExcelIsFun kindly share the M code playlist

@RossWalks 5 лет назад

Incredibly helpful, thanks Mike. The Dax measure you highlighted briefly seemed very complex. Do you have any courses on Dax measures like these you could point me to? I’m trying to be less reliant on calculated columns but struggling to get out of the excel mindset. Thanks!

@excelisfun 5 лет назад

Yes, this MSPTDA course teaches the foundation of DAX. Did you watch MSPTDA, #15, 16, 18 and 19? After those, there is this playlist: ru-vid.com/group/PLrRPvpgDmw0nglJ9yX2XT5-K1A_AKHpvW Actually 15, 16, 18 and 19 are the first 4 vids in the playlist.

@RossWalks 5 лет назад

ExcelIsFun You sir, are the man. Thank you so much.

@excelisfun 5 лет назад

@@RossWalks Thanks for those comments and Thumbs ups on each video, Ross : )

@shubhampawar8506 2 года назад

❤️

@hosseinhosseinpoor9561 2 года назад

top

@othnielglover-tay6799 5 лет назад

Yass first!

@excelisfun 5 лет назад

Yes, Trophy for you and Ka$hMoney!!!!!

@jaitiwari241 Год назад

I am Using Excel 2029 but, not able to Upload such a heavy file of call data.What to do?

@joad_lee 5 лет назад

Mike, have you tried VertiPaq Analyzer?

@excelisfun 5 лет назад

Yes, i have, but I have not used it since late 2017. It is quite useful.

@ogwalfrancis 5 лет назад

Thanks Mike, In Ctrl+Shift+Enter:Excel Array Formulas video series, you used table of content, i don't know whether you have a video on how to create table of content in Excel, am really in need of knowing how to create a table of content in Excel. Thanks alot.

@excelisfun 5 лет назад

I do not have a video for TOC, but that CSE woprkbook does have a wild example. The formula I used there is not necessary for a simple TOC. You can create your own links on your TOC sheet by; 1) Slect a cell, 2) Ctrl + K to open Hyperlink dialog box, 3) Place in this document.

@ogwalfrancis 5 лет назад

Thanks you very much Mike

@chandu883 3 года назад

I can't download the resource files from discription: Error: Forbidden You don't have permission to access this resource. Apache/2.4.29 (Ubuntu) Server at people.highline.edu Port 80

@faustopiovan977 5 лет назад

Great! Is anybody aware about a solution for managing 2 datasets with one single slicer? Where can I look pls? THKS

@mehdihammadi6145 5 лет назад

First result using google search : ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-AibLsRM-QbI.html

@excelisfun 5 лет назад

@@mehdihammadi6145 Great video by Mr Excel! Thanks for the assist, Mehdi : )

@igorzinenko4182 5 лет назад

why video is in a so low resolution? I can not see it higher than 360p resolution. Can would it be fixed somehow?

5 лет назад

still processing

@igorzinenko4182 5 лет назад

@gingerLunethanks, it has worked.

@excelisfun 5 лет назад

It is just RU-vid Processing when it is first uploaded : (

@GeertDelmulle 5 лет назад

I saw it immediately in 1080p. But then again I'm a frequent viewer. ;-) -- LOL!

@igorzinenko4182 5 лет назад

@@excelisfun never mind, reloading fixed this problem. thanks.:)

@tigerdataimarticus7088 3 года назад

M Code playlist plz

@TylerWhieler 2 года назад

thats kilobytes sir but thanks for tutorial

@kutra100 5 лет назад

Hi Mike Thanks for another wonderful video! Is this a topic you could cover as well if possible (see link)? stackoverflow.com/questions/53197587/power-query-check-if-string-contains-strings-from-a-list The areas where the code specifically uses TextsLists = List.Transform(Texts, each Text.Split(_," ")) doesn't appear very intuitive. For example using the "_" in Text.Split when the function expects text values seems a bit strange oddly.

@mattschoular8844 5 лет назад

The new way to spell "awesome" is "p-o-w-e-r q-u-e-r-y". Thanks Mike..

@excelisfun 5 лет назад

I love that!!!! I agree : )

@excelisfun 5 лет назад

You are welcome, Matt!

@jc7671 4 года назад

Downloaded this morning. 788MB. I suppose COVET-19 has increased the reliance on services.

@excelisfun 4 года назад

I am pretty sure with the techniques shown in this video you can get that file size way down : )

@jc7671 4 года назад

@@excelisfun sorry Mike. 788MB was the size of the CSV file downloaded.

@excelisfun 4 года назад

@@jc7671 Ya, that is the size of the file I downloaded too, in the video. At least that is about the size I remember...

@excelisfun 4 года назад

@@jc7671 O, I just looked, you are right it was smaller: 744 MB

@ZRBBL 5 лет назад

Superhero! Why didn't save IronMan!

@excelisfun 5 лет назад

Becasue they did not do the Power Query Data Modeling right!!! ; )

@olaspanglund3033 5 лет назад

Nice video but I think you filtered out the last day of 2018 when not specifying 23:59:59 as the time.

@excelisfun 5 лет назад

Yes, thanks for the help. You are right, Ola : ) Here is what it should have been: = Table.SelectRows(#"Changed Type", each [Original Time Queued] >= #datetime(2017, 1, 1, 0, 0, 0) and [Original Time Queued] < #datetime(2019, 1, 1, 0, 0, 0))

@NoShadowOfDoubt1 5 лет назад

Best is greater than 12/31/2016 and less than 1/1/2019

@excelisfun 5 лет назад

@@NoShadowOfDoubt1 , is there a difference effective between "greater than 12/31/2016 and less than 1/1/2019" and "greater than or equal to 1/1/2017 and less than 1/1/2019" ? Why would you prefer one over the other? I guess your proposal does have one less equal sign : )

@NoShadowOfDoubt1 5 лет назад

@@excelisfun I try to frame the focus period completely and look at it from the outside, not being in it with => or

@excelisfun 5 лет назад

@@NoShadowOfDoubt1 Frame From Outside: FFO : )