Forum Discussion
Advocate IData from Excel to Power BI
i'm working on an excel file for last year data Q4 it's in the attached format I find it hard to link on power bi, as my current dashboard file has Dates and this one has only Q4 and weeks, also the formating is really messy as you can see , Any advice please
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
9 Replies
rohit1991Super User
Hii 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.
abdo132that'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!
AshokKunwarContinued Contributor
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
- cengizhanarslan
This is a data-shape problem, not a Power BI limitation. You can use this data, but you must normalize it first. Trying to model it “as is” will always be painful.
- Dates are spread across columns (Q4 / week headers)
- No actual date key to relate to your Date table
- Mixed semantics in headers (Quarter + Week + Year)
- Impossible to create a clean relationship in this shape
Power BI expects rows = events, columns = attributes.
Recommended fix:
1) Unpivot the week columns
In Power Query:
- Keep Product Level 2
- Select all week value columns
- Unpivot Columns
You’ll get:
Product | Attribute | Value TV | 2025-Q4 Week 1 | 134,071Rename:
- Attribute → YearQuarterWeek
- Value → Amount
2) Split Year / Quarter / Week
Split YearQuarterWeek into:
- Year
- Quarter
- WeekNum
Now you have proper fields:
Product | Year | Quarter | Week | Amount3) Create a Week → Date mapping
Since your main model is date-based, you need one of these:
Best option (recommended):
- Create a Date table with Year + WeekNumber
- Add WeekStartDate (ISO week logic)
- Relate:
Fact[Year + Week] → Date[Year + Week]If you don’t know exact dates:
- Create a synthetic date like:
WeekDate = DATE ( Year, 1, 1 ) + ( Week - 1 ) * 7 - AshokKunwar
Re: Handling Weekly/Quarterly Excel Data with Messy Formatting
Hello. This is a common data cleaning challenge. To link this Excel file to your dashboard, you need to use the Unpivot feature in Power Query.
First, import the file and use the Remove Top Rows feature to get rid of the messy headers. Next, select your static columns and choose Unpivot Other Columns. This will turn your weeks into a vertical list.
Since your file only has week numbers, you must create a date link. You can add a custom column to assign a specific date to each week. For example, assign the first Monday of Q4 to Week 1.
Once you have a real date column, you can create a relationship with your main Date Table in the Model view.
I hope this helps you clean the data. If this works, please mark this as an Accepted Solution.
Happy New Year!
Vishwanath
- ryan_mayu
You can unpivot tables to convert them from a two-dimensional structure to a one-dimensional structure. That is the correct table layout that we are usually using in the model.
If this still does not work, pls provide the sample data (not the screenshot ) and the expected output
v-lgarikapatCommunity Support
Hi abdo132 , Thank you for reaching out to the Fabric Community.
cengizhanarslan , AshokKunwar , ryan_mayu , krishnakanth240 Thanks for your prompt response.
abdo132 I wanted to follow up and confirm whether you’ve had the opportunity to review the information shared by cengizhanarslan , AshokKunwar , ryan_mayu , krishnakanth240 If you have any questions or need further clarification, please don’t hesitate to reach out.
We truly appreciate your engagement and thank you for being an active part of the community.
Best Regards,
Lakshmi.
- v-lgarikapat
Hi abdo132 ,
I just wanted to check if your issue has been resolved. If you still have any questions or need help, feel free to reach out I’m happy to assist.
Thank you for being an active part of the community. Looking forward to hearing from you!
Best regards,
Lakshmi
