masterexcelstuff-blog

[masterexcelstuff-blog]

Excel Magic Trick 1498

Excel Magic Trick 1498

DESCRIPTION

In this video learn how to Transform Two Fact Tables (Header Invoice and Invoice Line Item Tables) with Dimension Tables into a Proper Star Schema using Power Query inside Power BI Desktop. The Power Query Steps can be used in Excel or Power BI. Learn how to use Power Query to Import non-Start-Schema Model,…

[masterexcelstuff-blog]

Excel Magic Trick 1496

Excel Magic Trick 1496

DESCRIPTION

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…

[masterexcelstuff-blog]

Excel Magic Trick 1495 Part 2

Excel Magic Trick 1495 Part 2

DESCRIPTION

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…

[masterexcelstuff-blog]

Excel Magic Trick 1495 Part 1

Excel Magic Trick 1495 Part 1

DESCRIPTION

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…

[masterexcelstuff-blog]

Excel Magic Trick 1494

Excel Magic Trick 1494

DESCRIPTION

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…

[masterexcelstuff-blog]

Excel Magic Trick 1493 Part 2 DESCRIPTION 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.

[masterexcelstuff-blog]

Excel Magic Trick 1493 Part 1 DESCRIPTION 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.

[masterexcelstuff-blog]

Excel Magic Trick 1492

Excel Magic Trick 1492

DESCRIPTION

In this video learn how to do Approximate Match Lookup in a DAX Calculated Column using two different methods.

Topics:

1. Introduction & look back to last video where we used MAX Function on Discount Column

2. DAX LOOKUPVALUE Function, DAX Approximate Match Lookup

3.Foreign Key, Relationship & RELATED Function, DAX…

[masterexcelstuff-blog]

Excel Magic Trick 1491 DESCRIPTION Survey Results for “Lookup Latest Product Price” Winner of Free Book WORKBOOKS

[masterexcelstuff-blog]

Excel Magic Trick 1490 DESCRIPTION In this video learn how to create a DAX Calculated Column for Exact Match Lookup and Approximate Match Lookup.

[masterexcelstuff-blog]

Excel Magic Trick 1489 DESCRIPTION In this video learn how to create a single Excel formula that will lookup a product price and lookup the volume / quantity discount and deliver the correct discounted Product Price.

[masterexcelstuff-blog]

Excel Magic Trick 1488 DESCRIPTION Vote For Favourite Formula to Lookup Correct Price Based on Effective Date WORKBOOKS

[masterexcelstuff-blog]

Excel VBA 1.5

1: Open the Visual Basic Editor

See How

2: Click View –> Immediate Window to open the Immediate Window (or Ctrl + G ) 3: You should see the Immediate Window at the bottom on VBE

This window allow you to directly test some VBA code. So let’s start, type the following into the console:

?Worksheets.

VBE has intellisense and then it should open a tooltip

Select .Count in the list…

[masterexcelstuff-blog]

Excel Magic Trick 1487 DESCRIPTION In this video learn how to Lookup the correct product price based on the latest Effective Date.

[masterexcelstuff-blog]

Excel VBA 1.4

1: Open the Visual Basic Editor

See How

2: Click Insert –> Module to add a new Module

3: Type the following code in the new module :

Sub hello()       MsgBox “Hello World !” End Sub

4: Click on the green “play” arrow (or press F5) in the Visual Basic toolbar to run the program: 5: Select the new created sub “hello” and click Run 6: Done, you should see the window above

[masterexcelstuff-blog]

Excel Magic Trick 1486 DESCRIPTION In this video learn how to Lookup the correct product price based on the latest Effective Date.

[masterexcelstuff-blog]

Excel VBA 1.3

The procedure describes how to add an Object library reference, and afterwards how to declare new variables with reference to the new library class objects.

The example below shows how to add the PowerPoint library to the existing VB Project. 

As can be seen, currently the PowerPoint Object library is not available. Step 1: Select Menu Tools –> References… Step 2:Select the Reference…