Forum Discussion
Post PatronNeed to make a data column a Header
I need to make a year column. So the data shows 2024 and I need this to be a header. But I'll also need new headers to be added each year (2025, 2026 ect). So I'm not sure how to make this work. I did see solutions for "M Query" but since those years aren't avail yet not sure how to get it to work.
Current data shows this: (please note we only have data starting in October.
| Year | Month | CSQ Name | Calls Presented | |||
| 2024 | January | HS_ChildSupport | ||||
| 2024 | February | HS_ChildSupport | ||||
| 2024 | March | HS_ChildSupport | ||||
| 2024 | April | HS_ChildSupport | ||||
| 2024 | May | HS_ChildSupport | ||||
| 2024 | June | HS_ChildSupport | ||||
| 2024 | July | HS_ChildSupport | ||||
| 2024 | August | HS_ChildSupport | ||||
| 2024 | September | HS_ChildSupport | ||||
| 2024 | October | HS_ChildSupport | 637 | |||
| 2024 | November | HS_ChildSupport | ||||
| 2024 | December | HS_ChildSupport |
We want it to show:
| Month | 2021 | 2022 | 2023 | 2024 |
| January | 1,722 | 1,521 | 1,303 | 1,240 |
| February | 1,532 | 1,332 | 1,173 | 1,135 |
| March | 1,660 | 1,380 | 1,334 | 1,179 |
| April | 1,550 | 1,375 | 1,266 | 1,197 |
| May | 1,340 | 1,274 | 1,277 | 1,154 |
| June | 1,373 | 1,403 | 1,246 | 1,068 |
| July | 1,369 | 1,208 | 1,130 | 1,235 |
| August | 1,266 | 1,475 | 1,391 | 1,204 |
| September | 1,196 | 1,247 | 1,084 | 1,112 |
| October | 1,174 | 1,266 | 1,121 | |
| November | 1,176 | 1,134 | 1,029 | |
| December | 1,104 | 1,059 | 875 |
Load your data into Power Query:
Select your data range and go to Data > From Table/Range to load it into Power Query.
Pivot the data:
In Power Query, select the Year column.
Go to the Transform tab and click on Pivot Column.
In the Values Column dropdown, select Calls Presented.
Click Advanced Options and choose Don't Aggregate.
Fill down missing values:
Select the Month column.
Go to the Transform tab and click on Fill Down.
Add new year columns dynamically:
To ensure new year columns are added dynamically, you can create a custom function in Power Query to handle future years. Here’s a basic example of how you can do this:
let
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
PivotedTable = Table.Pivot(Source, List.Distinct(Source[Year]), "Year", "Calls Presented"),
FilledDownTable = Table.FillDown(PivotedTable, {"Month"})
in
FilledDownTableLoad the transformed data back to Excel:
Click Close & Load to load the transformed data back into Excel.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!
1 Reply
- saud968
Memorable Member
Load your data into Power Query:
Select your data range and go to Data > From Table/Range to load it into Power Query.
Pivot the data:
In Power Query, select the Year column.
Go to the Transform tab and click on Pivot Column.
In the Values Column dropdown, select Calls Presented.
Click Advanced Options and choose Don't Aggregate.
Fill down missing values:
Select the Month column.
Go to the Transform tab and click on Fill Down.
Add new year columns dynamically:
To ensure new year columns are added dynamically, you can create a custom function in Power Query to handle future years. Here’s a basic example of how you can do this:
let
Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],
PivotedTable = Table.Pivot(Source, List.Distinct(Source[Year]), "Year", "Calls Presented"),
FilledDownTable = Table.FillDown(PivotedTable, {"Month"})
in
FilledDownTableLoad the transformed data back to Excel:
Click Close & Load to load the transformed data back into Excel.
Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!