Download Excel START File: https://ift.tt/2OjjG41
Download Zipped Folder with Text Files: https://ift.tt/2yAuGiN
Download Excel FINISHED File: https://ift.tt/2Omigpl and https://ift.tt/2yDQGsS
Download pdf Notes about Power Query: https://ift.tt/2Okxdsa
Assigned Homework:
Download Excel file and complete the homework:
https://ift.tt/2yIVa1J
Examples of Finished Homework:
https://ift.tt/2OoIUxZ
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
Tag: Power BI
Using Locale in Power Query Power BI: Import & Append Text Files from Different Countries – MSPTDA 12
Download Excel START File: https://ift.tt/2I5RStl
Download Zipped Folder with Text Files: https://ift.tt/2pwvCRa
Download Excel FINISHED File: https://ift.tt/2Ic3LhL
Download Power BI Desktop FINISHED File: https://ift.tt/2psTK74
Download pdf Notes about Power Query: https://ift.tt/2I7y2y9
Comprehensive video about using Locale Settings so that Power Query interprets Dates and Numbers from different parts of the world correctly. In this Video learn about how to use the āUsing Localeā¦ā Feature and Regional Settings to import Text Files from Different Countries so that Dates and Numbers in Different Formats can be interpreted correct, and the multiple Text Files and be Appended into a single table. Also see how to change the Locale settings on individual columns.
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. (00:25) Text Files from Different Countries have Different Date and Number Formats
3. (02:40) Change Regional Settings in Power Query and Power BI Desktop
4. (04:28) Using Localeā¦ Feature on Single Columns to interpret Dates and Numbers Correctly
5. (06:50) Convert ISO Dates to Proper Dates in Power Query
6. (08:04) Power BI Desktop: Import Multiple Text Files with Different Date and Number Formats From Folder and Append. See 1) Create Table in Power BI Desktop, 2) Build Custom Function 3) Import Text Files From Folder and Append
7. (20:30) Summary
Assigned Homework:
Download pdf file with homework description: https://ift.tt/2psyxu0
Zipped Text Files: https://ift.tt/2I9op1C
Example of Finished Homework in Excel: https://ift.tt/2pvzUZ5
Which Power Query Steps Are Used in SQL Query Folding? āView Native Queryā feature! – MSPTDA 11.5
Download Excel FINISHED File: https://ift.tt/2wZE5jF
Download Power BI Desktop FINISHED File: https://ift.tt/2O4YUkv
Download pdf Notes about Power Query: https://ift.tt/2wZVp8t
In this Video discusses the new āView Native Queryā feature in Power BI Desktop Power Query and Office 365 Excel Power Query to determine which of the Applied Steps are sent back to the SQL Server Database as part of Query Folding.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Power Query to Import from SQL Server Database in Excel or Power BI Desktop – MSPTDA 11
Download Excel START File: https://ift.tt/2Mfed8h
Download Excel FINISHED File: https://ift.tt/2wZE5jF
Download Power BI Desktop FINISHED File: https://ift.tt/2O4YUkv
Download pdf Notes about Power Query: https://ift.tt/2wZVp8t
Practice Problems: Assigned Homework:
Download homework file (Practice Problems) : https://ift.tt/2O5PODZ
Example of Finished Homework: https://ift.tt/2O5PODZ
In this Video learn how to connect to an SQL Server Database and extract and transform data using Power Query in Excel and Power BI Desktop.
Topics:
1. (00:16) Introduction
2. (00:32) What is an SQL Server Database
3. (02:19) The Goal of our Queries and a look at the end result reports in Excel
4. (03:04) Comparing and Contrast using 1) Using Power Query User Interface or 2) Writing SQL Code in Power Query
5. (04:46) Example 1: Use Power Query User Interface to connect to SQL Server and Extract, Transform and Load Data.
6. (11:27) Example 2: Write SQL Code to connect to SQL Server and Extract, Transform and Load Data.
7. (14:44) Example 3: Using Power BI Desktop to connect to SQL Server and Import multiple Tables.
8. (18:29) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)
Learn how to deal with Power Query Error: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Two solutions are presented in this video.
Download Files: Excel Start: https://ift.tt/2L7OwpO
Zipped Folder: https://ift.tt/2PShOvY
Download Excel FINISHED Files: https://ift.tt/2MsL7Hs
Download pdf Notes about Power Query: https://ift.tt/2wkNW2K
Assigned Homework – these are problems for you to practice your new M Code skills:
Download Excel File with Homework: https://ift.tt/2MmtxFf
Example of Finished Homework: https://ift.tt/2L7OxtS
Chris Webbās blog about this topic: https://ift.tt/2NATkG3
Ken Puls blog about this topic: https://ift.tt/2PShRb8
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column – MSPTDA 10
Download Power BI Desktop START File: https://ift.tt/2BXmQEV
Download Power BI Desktop FINISHED File: https://ift.tt/2MZj7e5
Download pdf Notes about Power Query: https://ift.tt/2BXmSg1
Download Excel File with parallel Excel Example: https://ift.tt/2ojpNpi
Assigned Homework:
Download pdf file with homework description: https://ift.tt/2PfRSts
Example of Finished Homework in Power BI Desktop: https://ift.tt/2ojpP0o
In this Video learn Power Query M Code and Custom Functions to calculate Moving Annual Toatls.
Topics:
1. (00:15) Introduction
2. (01:10) Comment from YouTube that inspired the video. Verbal Description of the Data Model Transformation we want to make, including the Moving Annual Total Calculation.
3. (02:07) Thanks to Bill Szysz for Custom Function.
4. (02:18) Excel Example of Moving Annual Total
5. (03:30) Why Power Query and not Excel or DAX?
6. (03:43) Look at final solution and Custom Function to see what we are trying to accomplish, including a method to filter a table with in a Custom Column in Another Table and have the formula see criteria from the the Inner Table and the Outer Table.
7. (05:37) Step 1: Look at how we imported files
8. (06:07) Step 2: Extract a Sorted Unique List from the source Facet Table. Use Production Operator to get a List, then use the Table.Distinct and Table.Sort functions.
9. (07:31) Step 3: M Code to create a Crossjoin of all combinations of Months and Product Names with the steps: Extract Column, Convert to Start of Month, Extract Min and Max Dates, use List.Dates function to create range of dates, then merge using Custom Column to get all combinations of Months and dates.
10. (14:39) Step 4: Group BY Date and Product to get Monthly Totals.
11. (16:25) Step 5: Create Final Table with the steps: Merge Step 3 and Step 4, Remove Nulls, Add Custom Column to get One Year Back.
12. (20:15) Step 5: Sort and how it is different than Excel Sport.
13. (21:25) Step 5: Table.Buffer Function allows us to Buffer the Internal Table to prevent a call to the source table for every row in the table.
14. (22:22) Step 5: create Custom Column with Function to Calculate Moving Annual Totals (MAT).
15. (28:41) Add new data to test if everything updates
16. (29:06) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
MSPTDA 03: Power Query Introduction: Importing & Transformation Data in Excel & Power BI Desktop
Download Excel START File: https://ift.tt/2tztMRF
Download Access START File: https://ift.tt/2yOpICh
Download Excel FINISHED File: https://ift.tt/2N426wB
Download Power BI FINISHED File: https://ift.tt/2yQjKRf
Download pdf Notes about Power Query: https://ift.tt/2tAehsO
In this Video learn all about Power Query. A complete Introduction to all aspects of Power Query.
Topics:
1. (00:12) Introduction
2. (02:26) Example 1: Clean and Transform Data in Excel. Look at Excel Power Query User Interface & M Code. Look at Locations to Load Data. Edit, Delete and Add Steps to Power Query Solution. Add new data and Refresh Report and Power Query Transformation.
3. (27:20) Example 2: Extract & Import, Clean & Transform and Load Data from Relational Access Database into Excel Power Pivot Data Model and create Star Schema.
4. (40:37) Example 3: Extract & Import, Clean & Transform and Load Data From Multiple Text Files into Power BI Desktop Data Model. We will Combine all Text Files into Single Table.
5. (52:54) Example 4: Replace Complex Excel Array Formulas with Simple Power Query Solution. See how to Extract a Sorted Unique List.
6. (55:50) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Microsoft Power Tools for Data Analysis: Dashboards & Reports. Class Introduction Video. MSPTDA #01.
Download Excel & pdf Files: https://ift.tt/2tiUtJK
This video introduces the topics that will be covered in this Highline College BI 348 Class:
Name of Class:
BI 348 ā Microsoft Power Tools for Data Analysis:
ā¢ Power Query
ā¢ Power Pivot
ā¢ DAX
ā¢ Power BI Desktop
ā¢ Excel
For Creating:
ā¢ Data Models, Reports, Dashboards and Analytics
Taught by Mike excelisfun Girvin, Excel MVP 2013-2018
ā¢ A class about connecting to multiple source of data, transforming the data into a refreshable & dynamic data model, and building reports and dashboards to provide insightful and actionable information.
Prerequisites for this class:
ā¢ Busn 216: Excel Basics, https://www.youtube.com/playlist?list=PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
ā¢ Busn 218: Advanced Excel, https://www.youtube.com/playlist?list=PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
ā¢ Busn 210: Business Statistics, https://www.youtube.com/playlist?list=PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj
What Version of Excel:
ā¢ Office 365 (updated each month)
What Version of Power BI Desktop:
ā¢ Free Tool we will download (update each month)
Over View of Topics for the class:
1. Data Analysis / Business Intelligence terms and concepts that we will learn in this class:
ā¢ Proper Data Set
ā¢ Fact Table
ā¢ Dimension Tables
ā¢ Relationships
ā¢ Star Schema
ā¢ ETL
ā¢ Measures
ā¢ Dashboards
ā¢ SQL
ā¢ Data Warehousing
ā
2. Learn how to use Excel Power Query:
ā¢ Import Data from multiple sources
ā¢ Clean and Transform Data
ā¢ Create Data Components for Star Schema Data Models
ā¢ Load Data To Excel, the Data Model and Connection Only
ā¢ Replace Complicated Excel Solutions with Power Query Solution
ā¢ Use the Power query User Interface to create Power Query Solutions
ā¢ Learn about the Case Sensitive, Function-based M Code Language that is behind the scenes in Power Query
3. Learn how to use Excel Power Pivot:
ā¢ Excel Power Pivot provides:
i. Data Model where we can have multiple tables, formulas and relationships (Star Schema)
ii. Columnar Database to hold “Big Data” and process quickly over that “Big Data”
iii. New Formula Language called DAX:
1. Many More Calculations than in Standard PivotTable
2. Build One Formula that can work in many reports
3. Add Number Formatting to Formulas
ā¢ Excel Power Pivot to:
i. Replace VLOOKUP Formulas and Single Flat PivotTable Data Source with Multiple Tables, Relationships in the Data Model to create more efficient Reports & Dashboards
ii. Use Power Pivot Columnar Database to hold millions of rows of data
iii. DAX formulas have more Power than Standard PivotTable Calculations
4. Learn about Building Star Schema Data Models:
a. Why they are important in Power Pivot and Power BI Desktop
b. How to build them using:
i. Power Query
ii. Power Pivot
iii. DAX
iv. Power BI Desktop
5. Learn how to author DAX Formulas for Excelās Power Pivot & Power BI Desktop:
a. Calculated Column Formulas for Data Model
b. Measure Formulas for PivotTables
c. DAX Functions like SUMX, CALCULATE, RELATED, and Much Moreā¦
d. Lean why we must create Explicit rather than Implicit formulas
e. Learn how Row Context works in formulas
f. Learn how Filter Context works in formulas
g. Learn about Scalar & Table Functions
h. Use DAX Studio to visualize and analyze DAX Formulas
6. Learn how to use Power BI Desktop:
a. Power Query to import, clean, transform and create Star Schema Data Models
b. Create Relationships
c. Create DAX Formulas
d. Build Interactive Visualizations
e. Build Dashboards
ā
7. Learn how to use Excel:
ā¢ Spreadsheet Formulas & Functions
ā¢ Standard PivotTables
ā¢ Power Query
ā¢ Power Pivot
ā¢ Build Data Model PivotTables and the resultant Reports, Dashboards and Analytics
8. Building Refreshable, Insightful Dashboards
a. Build Excel Dashboards
b. Build Power BI Dashboards
9. Case Studies to practice using Power Pivot & Power BI Desktop for Reporting, Building Dashboards and Building Business Analytics Solutions
Excel Magic Trick 1503: Free Book Give Away EMT 1493 ā 1496 RAND & VLOOKUP Functions
In this video we give away a free book after the voting about which method was preferred in Allocating Header Invoice Amounts to the Line Item Transaction Table in these videos:
Related Videos:
EMT 1493: Excel Formulas & PivotTable: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1494: DAX & Power Pivot: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495: Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495 Part 02: Power Query w Group By Rows: Allocate Invoice Amounts To Line Item Table
EMT 1496: Power BI Desktop: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1497: Vote For Your Favorite āAllocate Invoice Header Amounts To Transaction Line Item Tableā
Power Query Power BI: Transform 2 Fact Tables to Star Schema Data Model (Invoice Data) EMT 1498
Power Query Power BI: Transform 2 Fact Tables to Star Schema Data Model (Invoice Data) EMT 1498
EMT 1497: Vote For Your Favorite āAllocate Invoice Header Amounts To Transaction Line Tableā
Vote Here: https://ift.tt/2xfRczM
Download Excel File: https://ift.tt/2KRKAuz
Download Excel File: https://ift.tt/2x73V7S Finished.xlsx
Download Excel File: https://ift.tt/2J2pHi5 Finished.xlsx
Download Excel File: https://ift.tt/2GFTSX2
Download Excel File: https://ift.tt/2J2daeu Finished.xlsx
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video learn how to take Invoice Shipping and Discount Amounts from an Invoice Level (Header) Transaction or Fact Table and allocate those amounts to the Invoice Line Item Level Fact Table so we can use the Line Level Product Criteria (Filter) to create a Shipping and Discount Amount Report by Product. This is a typical business problem where the grain (granularity) of the two Transaction / Fact Tables is different and in order to use a filter or condition / criteria from the Line Level Transaction / Fact Table we must first allocate the Header Level amounts to the Line Level.
Related Videos:
EMT 1493: Excel Formulas & PivotTable: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1494: DAX & Power Pivot: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495: Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495 Part 02: Power Query w Group By Rows: Allocate Invoice Amounts To Line Item Table
EMT 1496: Power BI Desktop: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1497: Vote For Your Favorite āAllocate Invoice Header Amounts To Transaction Line Item Tableā
Excel Magic Trick 1497
EMT 1495 Part 02: Power Query with Group By Rows: Allocate Invoice Amounts To Line Item Table
Download Excel Start File:https://ift.tt/2GFTSX2
Download Excel Finished File: https://ift.tt/2J2pBXM
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video learn how to take Invoice Shipping and Discount Amounts from an Invoice Level (Header) Transaction or Fact Table and allocate those amounts to the Invoice Line Item Level Fact Table so we can use the Line Level Product Criteria (Filter) to create a Shipping and Discount Amount Report by Product. This is a typical business problem where the grain (granularity) of the two Transaction / Fact Tables is different and in order to use a filter or condition / criteria from the Line Level Transaction / Fact Table we must first allocate the Header Level amounts to the Line Level.
In this video we use Power Query to take the three tables and convert them using Merges (Joins) and Custom Columns in Power Query into our final report. This video is different than Part 01 because we use Group By Rows to avoid two merges. This trick comes from Bill Szysz.
Topics in this video:
1. (00:06) Introduction
2. (01:47) Compare Steps from first video to the steps we do this this video
3. (03:00) Start Query From Excel
4. (04:09) Duplicate Import Source Data
5. (04:40) Multiply Columns using Multiply feature to calculate āLine Salesā
6. (05:51) Merge to lookup Product Weight
7. (06:57) Multiply Columns using Multiply feature to calculate āLine Weightā
8. (07:14) Group By to aggregate Line Sales and Line Weight in order to get Invoice / Header Level Amounts, but we also Group By Rows to save the Line Item Level data so we can use it later in the query
9. (08:59) Merge to lookup Invoice Level Shipping and Discount Amounts
10. (10:31) Divide Columns using Divide feature to calculate āInvoice % Discountā
11. (11:31) Expand Group By Rows step from earlier in query to get line item detail
12. (12:30) Multiply Columns using Multiply feature to calculate āLine Discountā
13. (12:54) Create Custom Column to calculate āLine Shipping Costsā
14. (13:37) Edit the previous two columns by editing the Table.AddColumn function and add the Power Query Function Number.Round
15. (15:00) Group By to get Final Product Report
16. (16:50) Summary
Other Related Videos:
EMT 1493: Excel Formulas & PivotTable: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1494: DAX & Power Pivot: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495: Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495 Part 02: Power Query with Group By Rows: Allocate Invoice Amounts To Line Item Table
EMT 1496: Power BI Desktop: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1497: Vote For Your Favorite āAllocate Invoice Header Amounts To Transaction Line Item Tableā
Search terms in this video: Header Detail Granularity Reporting Problem, Invoice Level, Invoice Detail Level Mismatch, Invoice Granularity Mismatch Reporting Issue, Granularity Invoice Reporting Problem: Invoice Total / Invoice Detail, Reporting Invoice Shipping & Discount at Invoice Detail Level?, Allocating Invoice Totals to Invoice Detail Level (Granularity Reporting Problem), Header Detail Granularity Reporting Invoice Example, Header/Line Item Transactions, Header / Line Item Transactions Reporting Issues, Allocating Invoice Shipping & Discount to Product Report, Allocating Invoice Shipping & Discount to Invoice Line Level, Allocating Invoice Shipping Discount to Invoice Line Level, Two Fact Tables, Different Granularity, How To Allocate Header Amounts to Line Item Fact Table so we can Slicer by Product?, Allocate Invoice Header Amounts, To Transaction Line Item Table, Two Transaction Tables, Different Granularity, Slice Report by Product
Excel Magic Trick 1495 Part 02