Forum Discussion
Advocate IData from Excel to Power BI
Hi abdo132
Create Year + Week columns in the Excel data
Year = 2024 (or last year)
Week = QuarterWeekNumBuild a Date/Week key
Example: Year * 100 + WeekCreate 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, ValueOne fact table
One Date table
Works with all visualsIf 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!
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)
- Open Power Query (Transform Data).
- Select your Product Level 2 and Quarter columns.
- Right-click and select "Unpivot Other Columns".
- This collapses your horizontal weeks into two vertical columns: Attribute (Week Number) and Value (The Amount).
- 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:
- In Power Query, go to Add Column > Custom Column.
- 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)
- Change the data type to Date.
Step 3: Performance Fix
- In the Model View, create a relationship between your new Date column and your Calendar[Date].
- 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
Super UserHii abdo132
Power BI works best with clean, tabular (long-format) data, so this Excel layout with quarters and week numbers as headers is hard to model. The recommended approach is to reshape the Excel file in Power Query: unpivot the Q4 week columns into rows, create proper columns like Date / Week / Quarter / Product / Value, and remove merged cells and formatting. Once the data is normalized, you can relate it to your existing Date table and reuse it easily in Power BI visuals.
Advocate Ithat's what I Did actually the problem is that i'm unable to link it with current year data , so i'm looking for a way to combine the file if possible
- krishnakanth240
Hi abdo132
Create Year + Week columns in the Excel data
Year = 2024 (or last year)
Week = QuarterWeekNumBuild a Date/Week key
Example: Year * 100 + WeekCreate 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, ValueOne fact table
One Date table
Works with all visualsIf 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!