Excel Tips
Creating the Power Pivot Table »
March 21, 2023
The Pivot Table dropdown offers 8 choices and most of them are fairly silly. PivotTable and Pivot Charts are obviously needed. Pivot table pros will love the Flattened PivotTable. But all of those choices in the middle are redundant.
Refer to a Related Table in a Formula »
March 20, 2023
I am entering a formula in the Fact table. I need to lookup a value from Product table. I’ve already defined a relationship between the tables.
Adding Calculations In the Power Pivot Grid »
March 17, 2023
Power Pivot introduces a new formula language called Data Analysis eXpressions or DAX. When you see a Power Pivot demo, the 2 million rows and the joining tables look impressive. But it turns out that DAX is the really jewel in Power Pivot.
March 15, 2023
One downside of Power Pivot is the inability to group daily dates up to months and years. The common workaround is to build a lookup table that contains every daily date from the earliest date to the latest date in your data.
Sort Month Name by Month Number »
March 14, 2023
Regular pivot tables use the Custom Lists dialog to automatically sort months into Jan, Feb, Mar sequence. Power Pivot doesn’t seem to be aware of Custom Lists and sorts into the alphabetic sequence of Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar. May, Nov, Oct, Sep.
Define Relationships Between Tables »
March 13, 2023
There are three different ways to define a relationship in Power Pivot. Say you want to link from the ProdID field in the Fact table to the ProdID field in the Products table. Follow these steps:
Use Joiner Tables Between Tables »
March 6, 2023
I have a top-level budget table with one row per month, region, product. It has 54 rows. I want to create a report comparing the budget table to an invoice table with hundreds of rows.