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: How to
MSPTDA 14: Power Pivot Into #2: Amazing Columnar Database Importing Millions of Rows Data into Excel
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
MSPTDA 14: Power Pivot Into #2: Amazing Columnar Database Importing Millions of Rows Data into Excel
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
Excel Magic Trick 1515: Yearly Income Statement Using SUMIFS Function, and NO Merge & Center
Download Excel START File: https://ift.tt/2Pul1RO
Download Excel FINISHED File: https://ift.tt/2y8TA9D
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video see how summarize the monthly Net Income Statement numbers into a Yearly Net Income Statement using the SUMIFS function. In the process, we will have to fix the Net Income Template and remove vthe Merge and Center feature and replace it with Center Across Selection.
Power Pivot Introduction #1: Relationships rather than VLOOKUP for PivotTable Report MSPTDA 13
Download Excel START File: https://ift.tt/2pB6fxA
Download Excel FINISHED File: https://ift.tt/2ynEf4C
Download Example of Large File Size using VLOOKUP: https://ift.tt/2pFVFp8
Download pdf Notes about Power Query: https://ift.tt/2ykLQ3J
This video introduces Excel Power Pivot and shows how to use the Relationship feature rather than VLOOKUP Function to reduce file size and allow user to pull fields for a PivotTable Report from Multiple Tables.
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:43) Look at VLOOKUP solution
3. (02:49) Introduction to What Is Power Pivot? Discuss what a Data Model is.
4. (05:46) Relationship feature
5. (09:16) Create PivotTable Report
6. (11:45) First Look at Implicit Measures
7. (13:02) Enable the Power Pivot Ribbon Tab
8. (13:37) First Look at Power Pivot Window and Data Model
9. (20:30) Summary
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
Mysteries of VLOOKUP Function Revealed! 15 Amazing Examples! (Excel Magic Trick 1514)
Need to learn all about VLOOKUP? Microsoft Excel MVP, Mike “excelisfun” Girvin, presents 15 amazing VLOOKUP examples, from the basics to advanced.
Download Excel START File: https://ift.tt/2p9IKeC
Download Excel FINISHED File: https://ift.tt/2OmxFBX
In this video Learn all about VLOOKUP. Learn from Basics to Advanced. See 12 amazing examples that will help you become a VLLOKUP Excel Master! Video taught by Microsoft Excel MVP and Excel YouTuber, Mike Girvin.
Topics:
(00:06) Introduction
1. (01:32) VLOOKUP is everywhere
(04:24) The different between Exact Match & Approximate Match Lookup
2. (06:00) VLOOKUP to Lookup Product Price (Exact Match Lookup)
3. (12:25) VLOOKUP to Lookup Straight Commission Rate (Approximate Match Lookup)
4. (18:32) Data Validation List & VLOOKUP
5. (21:45) Copy VLOOKUP Down a Column. Learn about Relative and Absolute Cell References.
6. (27:00) Dynamic Lookup Table: Excel Table feature
7. (31:47) Dynamic Data Source: Use Power Query to import Lookup Table
8. (36:14) VLOOKUP to Lookup Variable Commission Rate (Approximate Match Lookup)
9. (41:00) VLOOKUP & MATCH Function for Two-Way Lookup (Lookup Employee Information)
10. (48:02) Fuzzy Lookup = Incomplete Lookup Value
11. (51:50) VLOOKUP & IFNA Functions to Avoid Errors
12. (53:05) Partial Text Lookup & Converting Text Number to Number
13. (56:28) Avoid Zeros from VLOOKUP to Empty Cells
14. (58:27) Multiple Table Lookup with VLOOKUP and INDIRECT Functions
15. (01:04:40) Two Lookup Values
(01:08:29)Summary
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.
Max Consecutive Wins for Best City: Array Formula, Lookup 3-D Model – Excel Hash Competition
Excel Hash is a project created by Oz at Excel On Fire At YouTube and sponsored by Microsoft.
Goal of Excel Solution: Calculate the Max Consecutive Wins for Best City, then lookup the correct 3-D Model icon for the city with the most wins and have the solution dynamically update when new data arrives.
Download Files:
Excel Start File: https://ift.tt/2Nh9yqY
Data Source File: https://ift.tt/2PvqhnY
Excel Finished File: https://ift.tt/2NfGjEX
Playlist with competitor videos at:
Vote here:
https://ift.tt/2MOYPoA
6 Excel YouTubers:
excelisfun: https://www.youtube.com/user/ExcelIsFun
Bill from MrExcel: https://www.youtube.com/user/bjele123
Leila Gharani: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA
Mynda Treacy from My Online Training Hub: https://www.youtube.com/user/MyOnlineTrainingHub
Oz from Excel on Fire: https://www.youtube.com/user/WalrusCandy
Excel Campus: https://www.youtube.com/user/ExcelCampus
Topics in Video:
1. (00:01) Introduction and preview of finished Excel Solution
2. (02:30) Power Query to Import Data
3. (03:43) What does FREQUENCY Function do?
4. (06:04) Array Formula with MAX & FREQUENCY to calculate Max Consecutive Occurrences
5. (10:07) Lookup Formula to Lookup 3D Model
6. (11:11) What is a 3D Model?
7. (14:33) Form button and Macro to Update Data Source
8. (15:55) Refresh Data and see if Everything Updates
9. (16:09) Summary
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.