Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula: Excel Magic Trick 1552

About this tutorial: Video duration: 22:45 Download Excel Finished Files: Entire page with all Excel Files for All Videos: In this video learn how to Average a customers last three sales (last three dates) using Excel Worksheet Formulas, Power Query M Code and a Power Pivot & Power BI DAX Measure. Learn about the Table.Sort […]

Excel Magic Trick 1336: Power Query: Import Big Data Text Files: Connection Only or Data Model?

About this tutorial: Video duration: 11:35 Download File: See how to use Import 10 Text Files and Append (combine) then into a single Proper Data Set before making a PivotTable Report. Compare and Contrast whether we should use Connection Only or Data Model to store the data. 1. (00:18) Introduction & Look at Text Files […]

Import Power Pivot Data Model into Power BI Desktop (Excel Magic Trick 1365)

About this tutorial: Video duration: 3:36 Download File: See how to import an Excel Power Pivot Data Model with tables, relationships and DAX Measures into Power BI Desktop using the File (Upper Left icon), Import, Excel Workbook option in Power BI Desktop.

Excel Magic Trick 1436 Excel 2016: Combine 2 BIG Tables into 1 for PivotTable Report, No Power Pivot

About this tutorial: Video duration: 7:5 Download File: Start File: Finished File: Entire page with all Excel Files for All Videos: See how to use Excel 2016 to take a 900,000 row table and a 1,000,000 roe table and combine them into one in order to make a PivotTable Report. See how to use the […]

Excel Magic Trick 1331: Import Multiple Excel Files & Sheets into Excel: Power Query Get & Transform

About this tutorial: Video duration: 29:48 Download File: See how to use Power Query (Get & Transform) to import multiple Excel Workbook Files with Multiple Excel Sheets into a single Excel File in two different situations: 1. (00:18) Introduction to Append Multiple Workbooks and Merge Multiple Workbooks 2. (01:58) Append Multiple Workbooks into a single […]

Excel Magic Trick 1430: DAX Functions XNPV & XIRR for Irregular Cash Flow Net Present Value & IRR

About this tutorial: Video duration: 9:45 See how to use the DAX Functions XNPV and XIRR to calculate the Net Present Value and Internal Rate of Return of Cash Flows that occur at irregular date intervals. See how to use the DAX Functions XNPV and XIRR to calculate the Net Present Value and Internal Rate […]

Excel Magic Trick 1335: Power Query We Couldn’t Refresh The Connection Data Source Not Found Error

About this tutorial: Video duration: 1:43 Download File: In Power Query see how to use deal with the Refresh Error Message: We couldn’t refresh the connection. Here is the error message we got: [DataSource.NotFound] File or Folder: We couldn’t find the folder.

Excel Magic Trick 1349: Power Query with Input Variables from Excel Sheet to Extract Records

About this tutorial: Video duration: 18:2 Download File: See how to use Power Query (Get & Transform) with Input Variables from Excel Sheet to Extract Records and create a Revenue Report. Here are the steps: 1. (00:15) Introduction including Preview of Finished Solution and Preview of steps necessary to accomplish this goal. 2. (02:00) Import […]

Excel Magic Trick 1316: VLOOKUP with Multiple Lookup Tables: IF, INDIRECT, SWITCH, IFS, or CHOOSE?

About this tutorial: Video duration: 15:52 Download File: See how to use VLOOKUP when there are multiple Lookup Tables with these methods: 1. (00:15) Problem Set up 2. (01:48) IF Function 3. (05:57) IFS Function (Excel 2016 Insider Program) (Formula from XLarium at YouTube) 4. (08:47) SWITCH Function (Excel 2016 Insider Program) 5. (11:26) Names […]

Excel Magic Trick 1378: Average of Customer Total Sales by Product: Formulas, DAX or Array Formula?

About this tutorial: Video duration: 29:29 Download File: See how to calculate Average of Customer Total Sales by Product using Excel Formulas, Excel Array Formulas and DAX Measures in Excel Power Pivot: 1. (00:15) Introduction 2. (01:08) 2 Step Solution with SUMIFS and AVERAGEIF functions. 3. (07:21) 2 SUMIFS Array Formula 4. (11:26) FREQUENCY Array […]

Excel Magic Trick 1403: DAX Formulas: Explicit or Implicit Measures? Why Explicit is Better.

About this tutorial: Video duration: 11:22 Download File: Learn about the difference between Explicit and Implicit DAX Measures: 1. (00:10) Introduction to Explicit and Implicit DAX Measures 2. (01:33) Create Implicit Measures and see these six problems: 1) Number Field will use SUM Function, 2) Text Field uses COUNT, 3) You can’t add Number Formatting […]

Excel Magic Trick 1322: Backwards One To Many Relationship Report: Excel, Power Query or SQL?

About this tutorial: Video duration: 30:37 Download File: See how to use Excel, DAX, Power Query or SQL to Calculate Average GPA with one condition when the formulas need to ework backwards across a One To Many Relationship between Two Tables: 1. (00:16) Problem Introduction 2. (03:17) Excel Array Formula Solution using AVERAGE, IF and […]

Excel Magic Trick 1487: INDEX, MATCH & MAXIFS : Lookup Correct Price Based on Effective Date

About this tutorial: Video duration: 13:3 Download Excel File: Entire page with all Excel Files for All Videos: In this video learn how to Lookup the correct product price based on the latest Effective Date. The Lookup table has multiple listings of the product, each with a different effective date and price. In this video […]

Excel Magic Trick 1384: Import Excel Table or Sheet in Power Query or Power BI?

About this tutorial: Video duration: 4: Download File: Compare and Contract importing a Proper Data Set from either an Excel Sheet or an Excel Table using the Excel Table feature. See that the Excel Table is more robust because it will exclude data on a sheet that is outside the Excel Table. Question: Why do […]

Stylish MS Excel Sheet for Tracking Tasks

About this tutorial: Video duration: 1:31 This video explains, how to design an attractive Tracking Sheet with a professional look.

Introduction to Power Query & Power Pivot Data Model in Excel 2016 (Excel Magic Trick 1468)

About this tutorial: Video duration: 50:6 Download Excel File #1: Download Excel File #2: Download Zipped Folder: Entire page with all Excel Files for All Videos: Learn about how Topics: 1. (00:16) Introduction 2. (00:51) Discussion about the two different Data Ribbon Tabs and Get & Transform groups that you may see in Excel 2016 […]

Excel 2016 Dashboard Linking with Power Query – Pivot Table Dashboard Part 1

About this tutorial: Video duration: 4:49 Full 2.5hr Course with downloadable files In this video we will setup the first pivot table for our dashboard by connecting to our data via power query / get and transform. This will enable us to easily refresh all of our work with a click of a refresh button. […]

Excel Magic Trick 1472: Count Between (or Above and Below) Upper & Lower Limits (7 Examples)

About this tutorial: Video duration: 22:14 Download Files: Entire page with all Excel Files for All Videos: Learn about how to Topics: 1. (00:11) Introduction 2. (01:58) Ex 1: Between Upper & Lower Limits: =COUNTIFS Function 3. (04:45) Ex 2: Between Upper & Lower Limits: =DCOUNT Function 4. (07:00) Ex 3: Between Upper & Lower […]

Excel Magic Trick 1441 Power Query to Extract Items In List 1 That Are NOT In List 2: Left Anti Join

About this tutorial: Video duration: 5:52 In this video see how to use an Left Anti Join in Power Query to Extract Items in List 1 that are NOT in List 2. Download File: Start File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1441-1442Start.xlsx Finished File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1441-1442Finished.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm Related Videos for Comparing Two […]

Excel formulas and function#8|how to make Drop down list in excel in hindi||data validation in Excel

About this tutorial: Video duration: 8:58 how to make drop down list in excel 2013,how to make drop down list in excel,Excel Magic Trick,Data Validation List,Use data validation in Excel,Cell rage define in excel,Excel Trips & Tricks,Excel Magic Tricks,dropdown list,Data Validation,drop down list,how to use data validation in excel,how to make drop down list in […]