Data Modelling Dimension Table and Date-based Fact Table from Flat Excel Source

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

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:

Hi Anonymous ,

Thanks for the reply from MarkLaf  / lbendlin .

This is my Test table:

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!