Forum Discussion
Data Modeling/Filtering Advice Needed
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 |
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/1e16oHv9kTkHi, 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.
2 Replies
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.
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