Data from Excel to Power BI

Hi abdo132 

Create Year + Week columns in the Excel data
Year = 2024 (or last year)
Week = QuarterWeekNum

Build a Date/Week key
Example: Year * 100 + Week

Create the same key in your current year data
From your Date table (Year + WeekNum)

Append the Excel data to current data
Power Query → Append Queries
Ensure same columns: Product, Year, Week, Value

One fact table
One Date table
Works with all visuals

If you cannot Append
Use a bridge Date/Week table:

Year
Week number
Quarter

Relate both datasets to it.

Please give headup if the solution/approach is helpful. Thank You!

abdo132 

You must use Power Query to normalize the table and create a bridge to your existing Date dimension.

​Step 1: Normalize the "Messy" Layout (Unpivot)

1. ​Open Power Query (Transform Data).
2. ​Select your Product Level 2 and Quarter columns.
3. ​Right-click and select "Unpivot Other Columns".
   • ​This collapses your horizontal weeks into two vertical columns: Attribute (Week Number) and Value (The Amount).
4. ​Rename "Attribute" to Week.

​Step 2: Create the Date Link

​Since your dashboard relies on a Date Table, we need to turn "Week 1" into a real date:

1. ​In Power Query, go to Add Column > Custom Column.
2. ​Use this logic to find the start of the week for 2025-Q4: Date.AddDays(#date(2025,10,1), (Number.From(Text.Select([Week], {"0".."9"}))-1)*7)
3. ​Change the data type to Date.

​Step 3: Performance Fix

1. ​In the Model View, create a relationship between your new Date column and your Calendar[Date].
2. ​Update your Matrix visual to use the Date from your Calendar table instead of the raw Excel columns.

​Why this fixes the 230s Lag:

​Your Performance Analyzer shows that 99% of the load time is the DAX Query (214,502ms). By unpivoting and linking to a proper Date dimension, you allow the engine to use Filter Context instead of forced row-by-row iteration. This should reduce your load time from minutes to milliseconds.

​If this transformation fixed your layout and cleared the performance lag, please mark this as an "Accepted Solution" to help others with Excel-to-PBI migrations!

Best regards ,

Vishwanath