Month: June 2018

MSPTDA 03: Power Query Introduction: Importing & Transformation Data in Excel & Power BI Desktop

Posted on by Annie D

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.

View on YouTube

MSPTDA #02: Data Analysis Business Intelligence Terms: Microsoft Power Tools for Data Analysis Class

Posted on by Annie D

Download Excel File: https://ift.tt/2tvSYbd
Download pdf Notes: https://ift.tt/2lwxPdq

This video defines these terms and concepts:
1. (00:10) Introduction
2. (01:17) Data Analysis, Business Intelligence
3. (02:08) Query
4. (03:03) Goal of Data Analysis and Business Intelligence:
5. (03:30) Raw Data
6. (03:59) Proper Data Set, Field Names / Column Names / Header Names
7. (04:52) Data Type
8. (05:20) Naming things Smartly & Consistently
9. (07:41) Excel Table feature
10. (09:48) Grain or Granularity
11. (12:07) Keys: Primary Key & Foreign Key
12. (14:16) Fact Table / Transaction Table and Dimension Table / Lookup Table / Entity Tables
13. (17:04) Data Models
14. (19:43) Flat Table in Excel
15. (20:11) Relationships
16. (22:30) Star Schema Data Model
17. (23:32) Snow Flake Data Model
18. (25:12) Relational Database = Normalized Database
19. (25:46) Columnar Database
20. (27:52) Clean Raw Data, Transform Data Sets, Import Data
21. (29:00) ETL, Data Warehousing, SQL
22. (30:35) Concluding Picture and Summary

View on YouTube

Microsoft Power Tools for Data Analysis: Dashboards & Reports. Class Introduction Video. MSPTDA #01.

Posted on by Annie D

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

View on YouTube

Excel Magic Trick 1507: Count Empty in Non-Adjacent Cells (5 Examples) AREAS Function?

Posted on by Annie D

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

In this video see how Count Empty Cells in Non-Adjacent Cells. See that COUNTBLANKS will not work. Learn about the Functions COUNTA and AREAS. Learn how to build a Logical Formula. Also See How to use Defined Names to Defined Non-adjacent Cells as a single Defined Name.

Examples in this video:
1. (00:11) Introduction
2. (00:23) See that COUNTBLANKS will not work
3. (00:56) COUNTA Function and a Hard Coded Number
4. (02:21) COUNTA and AREAS Function
5. (03:54) Logical Formula that adds three Boolean Logical Tests.
6. (04:10) Learn about the Double Quotes as a Empty Cell or a Zero Length Text String in Logical Formulas.
7. (06:02) See how to define Non-Adjacent Cells as a single Defined Name
8. (06:02) Use the Non-Adjacent Cells as a single Defined Name inside both the AREAS and COUNTA Function for an easier to manage solution that allows us to edit the define name in a single location when we need to update the formula.
9. (09:02) Summary

View on YouTube

Excel Magic Trick 1506: Power Query to Extract All Numbers Listed in Single Cell and Add for Total

Posted on by Annie D

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

In this video see how to add all the numbers listed in a single cell using Power Query and the simple steps: 1) Split down Rows by a Space Delimiter, 2) Filter with Begins By “$”, 3) Group By to add numbers for each transaction.
Thanks to Bill Szysz for help with this trick.

View on YouTube

Excel Magic Trick 1504: Lookup Cell Address: 4 Examples: MATCH, ADDRESS, CELL function?

Posted on by Annie D

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

In this video see 4 different formulas to lookup a cell reference.
Topics:
1. (00:06) Introduction
2. (00:33) Formula #1: MATCH Function
3. (01:55) Formula #2: ADDRESS Function with MATCH & COLUMN Functions
4. (03:08) Formula #3: ADDRESS Function with MATCH, ROW & Column Functions
5. (04:11) Formula #4: CELL Function with INDEX and MATCH functions to lookup a cell reference
6. (05:32) Formula Evaluator to cell how formula calculated
7. (05:58) Conclusion

View on YouTube

Excel Magic Trick 1502: Lookup Based On Row Number with Data Validation For Row Number

Posted on by Annie D

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

In this video learn how to Lookup Based On Row Number with Data Validation For Row Number. Here are the topics:
1. (00:06) Introduction
2. (01:01) INDEX Function too pull data based on row number
3. (02:16) ROW Function to get first row in data set
4. (02:48) MATCH & REPT Functions to get last row number.
5. (05:23) Data Validation to limit on the row numbers in the correct range
6. (06:41) Text Formula for informative label
7. (07:48) Summary

View on YouTube

Excel Magic Trick 1501 Pt 02: INDIRECT Lookup Items From Different Sheets Based on Row Number

Posted on by Annie D

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

In this video learn how to use the INDIRECT Function to pull Data from a Spefified Sheet Based on Row Number. Also see a great INDEX formula to lookup a row number on a single sheet – formulas from Bill Szysz and pmsocho at YouTube.

View on YouTube

Excel Magic Trick 1501: INDIRECT Function: Pull Data from Different Sheet Based on Row Number

Posted on by Annie D

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

In this video learn how to use the INDIRECT Function to pull Data from a Different Sheet Based on Row Number.

View on YouTube

Excel Magic Trick 1500: Lookup All Products For Supplier & Join in Single Cell TEXTJOIN & IF

Posted on by Annie D

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

In this video learn how to lookup a supplier name in a lookup table and return all the products in a single cell. This is a Text Formula that is also an Array Formula. This formula uses the IF Function and the TEXTJOIN Function.

List all Products in Single Cell. TEXTJOIN Function, Array Formula. Text Formula.

View on YouTube