Power Query - Reduce Data Model Size, Transformations to Columnar Database Size

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Learn essential Power Query transformation techniques to optimize data model size and improve efficiency in this 25-minute tutorial. Explore strategies for reducing columnar database size when working with big data for business intelligence and data analysis. Discover how to apply concepts of columnar database storage to make informed decisions about tables, columns, and transformations in your final data model. Follow along as the instructor demonstrates importing and manipulating a large CSV file from a government website, filtering rows, removing columns, reducing column cardinality, and creating relationships. Create measures using DAX functions, build a dashboard with pivot tables and slicers, and learn how to effectively present data insights. Gain practical skills to enhance your Microsoft Power Tools for Data Analysis proficiency and streamline your data modeling process.

Syllabus

) Introduction.
) Download HUGE CSV File..
) Talk about How Columnar Database works and how we can reduce size..
) Rules for Efficient Data Modeling.
) Get Data From Government Web Site: https://data.seattle.gov/Public-Safety/Call-Data/33kz-ixgy.
) Import CSV Text File.
) Load Entire CSV File and look at Model Size..
) Filter Rows and look at Model Size..
) Remove Columns and look at Model Size..
) Reduce Cardinality of Columns with 1) Add Column for Time.
) Reduce Cardinality of Columns with 2) Transform Columns and get Date Only.
) Edit Time Column to Create only Hour and reduce size further..
) Edit Query can lead to trouble in subsequent steps. See how to fix the problem..
) Add Date Table.
) Create Relationship.
) Create Measure to Count Rows for Call Type. Use COUNTROWS DAX Function..
) Create Dashboard.
) Create Pivot Table.
) Filter to show Top 20, 911 Call Types.
) Show Values As in Data Model PivotTable.
) Sort Measure Biggest to Smallest.
) Add Slicers.
) Scrape Data form Web Site.
) Summary.