Tag: Productivity

Using Locale in Power Query Power BI: Import & Append Text Files from Different Countries – MSPTDA 12

Posted on by Annie D

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

View on YouTube

Mysteries of VLOOKUP Function Revealed! 15 Amazing Examples! (Excel Magic Trick 1514)

Posted on by Annie D

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

View on YouTube

Which Power Query Steps Are Used in SQL Query Folding? ā€œView Native Queryā€ feature! – MSPTDA 11.5

Posted on by Annie D

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.

View on YouTube

Power Query to Import from SQL Server Database in Excel or Power BI Desktop – MSPTDA 11

Posted on by Annie D

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.

View on YouTube

Max Consecutive Wins for Best City: Array Formula, Lookup 3-D Model – Excel Hash Competition

Posted on by Annie D

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

View on YouTube

Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)

Posted on by Annie D

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.

View on YouTube

Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column – MSPTDA 10

Posted on by Annie D

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.

View on YouTube

Excel Magic Trick 1513: COUNTIFS from Multiple Cells!?!? Array Formula or Logical Formula?

Posted on by Annie D

Download Excel File: https://ift.tt/2BNJeQM
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs

In this video see how to count values that are greater than a hurdle when the values in in noncontiguous cells (cells not next to each other). See an Array Formula that uses SUMPRODUCT and CHOOSE and a Logical Formula.

View on YouTube

MSPTDA 09 Power Query Complete M Code Introduction: Values, let, Lookup, Functions, Parameters, More

Posted on by Annie D

Download Excel START Files: https://ift.tt/2L7OwpO
Download Excel FINISHED Files: https://ift.tt/2MsL7Hs
Download pdf Notes about Power Query: https://ift.tt/2wkNW2K
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2MmtxFf
Example of Finished Homework: https://ift.tt/2L7OxtS

In this Video learn the basics of M Code, the computer language behind queries in Power Query.
Topics:
1. (00:15) Introduction
2. (03:46) Edit M Code: Applied Steps
3. (03:46) Edit M Code: Formula Bar
4. (03:46) Edit M Code: Advanced Editor
5. (09:50) Expressions
6. (09:50) let expressions
7. (17:34) Comments in M Code
8. (21:11) Values: Primitive, List, Record, Table, Function
9. (30:45) Lookup or Projection and Selection. Learn about Row Index Lookup and Key Match lookup
10. (42:50) Primary Keys
11. (50:20) Custom Functions
12. (57:44) Parmenter Queries
13. (01;02:27) Underscore Character _
14. (01:06:17) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

Excel Magic Trick 1512: Count Workers Employed 1 to 6 Years Based on Hire Date? 9 Examples

Posted on by Annie D

Download File: https://ift.tt/2AYwOFj
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs

In this video see how to count how many employees have worked for the company between 1 and 6 years, based on a hire date. See 8 examples of different formulas and Conditional Formatting.
Topics:
1. (00:06) Introduction
2. (01:13) TODAY Function
3. (01:44) EDATE Function for Lower Limit for counting between lower date and upper date. EDATE for upper limit formula too.
4. (03:08) COUNTIFS to count between lower and upper dates. Learn about how the comparative operator in COUNTISF requires quotes. Formula Counts Between a Lower & Upper Limit.
5. (04:41) AND Function Helper Colum for logical TRUE / FALSE formula. Learn about how the comparative operators in Logical Formulas do NOT require quotes.
6. (06:35) COUNTIFS function with TRUE criteria. Count Number of TRUE values.
7. (06:57) SUMPRODUCT Function to add the number of TRUE values. Add Number of TRUE values.
8. (08:47) Conditional Formatting Formula to highlight the employee records (highlight row) where the employee has worked for company between one to six years.
9. (11:40) One Complete Mashed Up Formula that does not require intermediate cells with formulas. Learn a lot of how you can copy and paste formula elements from intermediate cells into one final formula ā€“ huge mega formula.
10. (13:46) Summary

View on YouTube

MSPTDA 08.5: Power Query Group By Unique List or Consecutive Occurrences

Posted on by Annie D

Download Excel START Files: https://ift.tt/2MqR0km
Download Excel FINISHED Files: https://ift.tt/2MfNGfr
Download pdf Notes about Power Query: https://ift.tt/2veIr4P
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2M4R2Sl
Example of Finished Homework: https://ift.tt/2OjHnob

In this Video learn how to use Power Queryā€™s Group By feature to Group By and create a unique list with aggregate calculations or create a Group By Report based on Consecutive Occurrences of items in a given column with aggregate calculations.
Topics:
1. (00:15) Introduction
2. (00:37) What is Group By Report based on Consecutive Occurrences?
3. (01:27) Group By feature to Group By and create a unique list with aggregate calculations
4. (03:15) Learn about how Gear Icon can Disappear when you alter the M Code, which means the dialog box disappears.
5. (05:12) Learn about the difference between Duplicating a Query and Referencing a Query.
6. (05:12) Group By Report based on Consecutive Occurrences of items in a given column with aggregate calculations. Use the forth argument and GroupKind.Local
7. (07:27) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

Excel Magic Trick 1510: Conditional Format Row With Duplicates Based on Product & Color

Posted on by Annie D

Download Files: https://ift.tt/2LQIbEp
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs

In this video see how to color a row with conditional formatting using the COUNTIFS function, an Expandable Range and a Comparative Operator to convert formula to a Logical Formula. See how to use the Conditional Formatting Dialog Box with a Logical Formula. Also see how to use Conditional Formatting on an Excel Table, so new rows are formatted when new records are added..

View on YouTube