Data Modeling/Filtering Advice Needed

[wina-sunbelle]

The goal is to be able to visualize each vendor with a bar chart 2024-2025 (actuals) vs 2025-2026 (projections). I am stuck on how I should model this as the data received is according to weeks. As I try to work this, Power Query is blocking me from moving forward because the Column Names are being duplicated. I need to be able to filter by Pizza company. Below is sample data that is the current output. Expected output is noted below. Any advice on how to proceed will be greatly appreciated. ChatGPT suggested demoting the headers / transposing / splitting by delimiter but this ultimately did not result in the expected output. 

Sample Data / Current Output

Week Start	Week End	Week Num	Pizza Hut 24-25	Dominos 24-25	Joe's Pizza 24-25	Pizza Hut 25-26	Dominos 25-26	Joe's Pizza 25-26
June 10, 2024	June 16, 2024	24	123	98	75	252	353	150
June 17, 2024	June 23, 2024	25	70	106	69	200	315	150

Expected Output **Notice the duplicate column headers - This is currently blocking me***

Week Start	Week End	Week Num	Pizza Hut	Dominos	Joe's Pizza	Pizza Hut	Dominos	Joe's Pizza
			24-25	24-25	24-25	25-26	25-26	25-26
June 10, 2024	June 16, 2024	24	123	98	75	252	353	150
June 17, 2024	June 23, 2024	25	70	106	69	200	315	150

[amitchandak · Accepted solution]

@wina-sunbelle , In Power query Unpivot Other columns (Other than Week Start, Week End, Week Num)
Split by Delimiter , from end -> 2 columns . That will give Pizza Hut and Year separated 

Now you can create a Matrix visual with Type (Pizza Hut and Dominos) , and FY

if you have double  header refer 
https://youtu.be/1e16oHv9kTk

[MasonMA · Accepted solution]

@wina-sunbelle 

Hi, Your expected result cannot be completed in Power Query, but there is a way in reporting. 

Once you are done normalizing data in Power Query with the approach provided in earlier post. 

Select matrix and use 'outline' for layout under 'Layout and style presets'. Place the right fields in right rows, columns and values as in below picture. 

[amitchandak · Accepted solution]

@wina-sunbelle , In Power query Unpivot Other columns (Other than Week Start, Week End, Week Num)
Split by Delimiter , from end -> 2 columns . That will give Pizza Hut and Year separated 

Now you can create a Matrix visual with Type (Pizza Hut and Dominos) , and FY

if you have double  header refer 
https://youtu.be/1e16oHv9kTk