Solved: Data Modelling Dimension Table and Date-based Fact...

BIWorx · ‎02-17-2025

Hi team - wondering if someone can guide me please? I generally know how to create Dim & Fact tables from a flat (clean & well organized) Excel source file but the last step(s) escape me.

My Excel file of Food data has the following Columns:

Food Names

Food Description
Food Grouping

Week1 (Values for all Foods for Week 1)

Week2 (Values for all Foods for Week 2)

Week3 (Values for all Food's for Week 3)

etc.......

I've created my Food Dimension table where all the Food related data (Name, Description, Grouping) are stored.

For my Fact Table containing the values of the Foods by Date, Ideally I would like to have the Foods as their own columns and a 'Date' column (Week# in this example to keep things simple).

I am familiar with how to create a key to relate two tables but it does not work in this example as I have the date dimension to consider. I'm strugling to see how I can associate my Food Dimension table with this Fact table. Should I be leaving the 'dates' as the first row of my fact table? This makes creating the relationship between the two easy but reporting on each of the Foods 'over time' extra challenging.

The closest I've gotten is unpivoting the Fact Table and ending up with columns with the Food name (key), Week# and 3rd column for the value. This also presents challenges creating the reports. Not impossible - just not straight-forward.

What am I missing? (Happy to add some pics if that helps)?

Thanks in advance.

MarkLaf · ‎02-17-2025

The short answer is that you need to unpivot your week columns and relate you fact to a Dates table. Here is a walktrhough of an example - your particular business rules may require a different approach for some components.

Let's say you start with an imported workbook like the below (referred to as the 'xlsx' query in rest of post):

1) Construct your dimension table. I'd recommend adding an ID column

//Name: Food
let
    Source = xlsx,
    AddFoodId = Table.AddIndexColumn(Source, "Food ID", 1, 1, Int64.Type),
    SelectDimCols = Table.SelectColumns(AddFoodId,{"Food ID", "Food Names", "Food Description", "Food Grouping"})
in
    SelectDimCols

Food ID	Food Names	Food Description	Food Grouping
1	Trix	Placeholder_Trix	Breakfast
2	Cheerios	Placeholder_Cheerios	Breakfast
3	Eggs	Placeholder_Eggs	Breakfast
4	Soup	Placeholder_Soup	Lunch
5	Sandwich	Placeholder_Sandwich	Lunch

2) Construct your fact table. I've added the same ID step as that will be how we relate the table. The main work here is to calculate a date from your Week column.

//Name: Food Values
let
    Source = xlsx,
    RemoveDimCols = Table.RemoveColumns(Source,{"Food Names", "Food Description", "Food Grouping"}),
    AddFoodId = Table.AddIndexColumn(RemoveDimCols, "Food ID", 1, 1, Int64.Type),
    UnpivotWeeks = Table.UnpivotOtherColumns(AddFoodId, {"Food ID"}, "Week Name", "Food Value"),
    YearOfData = 2024,
    FirstDay = #date(YearOfData,1,1),
    AddWeekNumCalc = Table.AddColumn( 
        UnpivotWeeks, "WeekNumCalc", 
        each Int64.From( Text.Middle( [Week Name], 4 ) ), 
        Int64.Type 
    ),
    AddWeekStartCalc = Table.AddColumn( 
        AddWeekNumCalc, "WeekStartCalc", 
        each Date.AddWeeks( Date.StartOfWeek( FirstDay ), [WeekNumCalc] - 1 ), 
        type date 
    ),
    AddWeekStart = Table.AddColumn( 
        AddWeekStartCalc, "Week Start", 
        each List.Max( { FirstDay, [WeekStartCalc] } ), 
        type date 
    ),
    RemoveCalcCols = Table.RemoveColumns(AddWeekStart,{"WeekNumCalc", "WeekStartCalc"})
in
    RemoveCalcCols

Note that we are adding some "calc" columns to help us get the final "Week Start" column value; we're ensuring that the date is assigned to our "YearOfData" even if technically the week started in the previous year. If you have multiple years of data, you'll have to get years into your fact and calculate related dates accordingly.

3) Create a Dates table. This can be imported from a data source or consutrcted with either DAX or Power Query. Here is a simple table constructed with Power Query. See: https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

//Name: Dates
let

    YearOfData = 2024,
    YearDuration = Duration.Days(#date(YearOfData,12,31) - #date(YearOfData,1,1)) + 1,
    DateList = List.Dates(#date(YearOfData,1,1), YearDuration, #duration(1, 0, 0, 0)),
    ToTable = Table.FromList(DateList, Splitter.SplitByNothing(), type table [Date=date], null, ExtraValues.Error),
    AddMonth = Table.AddColumn(ToTable, "Month", each Date.Month([Date]), Int64.Type),
    AddMonthName = Table.AddColumn(AddMonth, "Month Name", each Date.MonthName([Date]), type text),
    AddWeek = Table.AddColumn(AddMonthName, "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)
in
    AddWeek

4) Load your tables and relate them. Food -1---M-> Food Values <-M---1- Dates

5) Configure your Dates table. Most important is to 'Mark as date table' if you want it to work well with time intelligence functions. Certain columns (e.g., MonthName in this case) you'll want to set the Sort By

6) Now you can start building a report. With this model, you can do a lot of visuals with simple drag and drop. Example:

View solution in original post

Anonymous · ‎02-17-2025

Hi @BIWorx ,

Thanks for the reply from MarkLaf / lbendlin .

This is my Test table:

Food Names	Food Description	Food Grouping	Week1	Week2	Week3	Week4
Apple	Sweet fruit	Fruits	10	12	14	15
Banana	Yellow fruit	Fruits	8	9	11	10
Carrot	Orange vegetable	Vegetables	7	8	9	10
Lettuce	Green vegetable	Vegetables	5	6	7	6

Create a separate date dimension table:

DateDimension = 
ADDCOLUMNS(
    CALENDAR(DATE(2025,1,1), TODAY()),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "Week#", "Week" & FORMAT(WEEKNUM([Date]), "0")
)

Since a direct relationship between the Week# of the date dimension table and the Week# of the Fact table would create a many-to-many relationship, create a separate Week dimension table:

WeekDimension = 
SUMMARIZE(
    DateDimension,
    [Week#],
    "StartDate", MIN('DateDimension'[Date]),
    "EndDate", MAX('DateDimension'[Date])
)

Enter Power Query to process the Fact table. Select Week1 - Week4 and then do a unpivot:

Unpivot result:

Creating Relationships:

Food Names in the Food dimension table with Food Names in the fact table.
Week# in the Week dimension table with Week# in the Fact table.
Week# in the Date dimension table with Week# in the Week dimension table.

With these steps, you can avoid many-to-many relationships while ensuring that you can analyze food data on a weekly basis.

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!