Excel Tips
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.
Reporting from the Smaller Side of the Relationship »
March 3, 2023
I am trying to report sales and quota. My sales table has hundreds of records. The Quota table has just one record per region. The pivot table created through the Data Model is not working.
Pivot from Multiple Tables Using The Data Model »
March 2, 2023
I have a lot of rows in a data table and then some lookup tables. Is there a faster way to create a report than building a bunch of VLOOKUP formulas?
Dive into M Code For More Power Query »
March 1, 2023
The Power Query interface provides an easy way for you to write steps in the M programming language. There are many things possible in M that can not be done in the interface.