Forum Discussion
Data Formatting for Slicer
Hi All,
TL;DR
How can I create a slicer for both month and year?
I'm a co-op student working on an accounting project for a company.
The data that I am using is being pulled directly from SQL but I have created a table in Excel to better represent my issue.
Currently, the data is formatted similarly to the image. Each month has its own column and there is also a column for the year.
I am hoping to be able to create a slicer for both month and year. To create a slicer for the months, I would ahve to transpose the table so that each month has its own row instead of column. This pose an issue however, if I want to also have a slicer for the year.
| Year | Jan Amount | Feb Amount | Mar Amount |
| 2017 | 0 | 6 | 4 |
| 2017 | 7 | 7 | 863 |
| 2017 | 0 | 6 | 5 |
| 2017 | 0 | 6 | 4 |
| 2017 | 8 | 76 | 4 |
| 2017 | 89 | 8 | 5 |
| 2017 | 6 | 8 | 8 |
| 2017 | 657 | 8 | 7 |
| 2017 | 7 | 8 | 4 |
| 2017 | 7 | 0 | 66 |
| 2017 | 4 | 5 | 6 |
| 2018 | 8 | 4 | 6 |
| 2018 | 45 | 9 | 4 |
| 2018 | 6 | 7 | 7 |
| 2018 | 76 | 8 | 6 |
| 2018 | 8 | 9 | 7 |
| 2018 | 6 | 978 | 6 |
| 2018 | 657 | 9 | 6 |
| 2018 | 78 | 6 | 6 |
| 2018 | 65 | 6 | 6 |
| 2018 | 7 | 9 | 6 |
| 2018 | 8 | 9 | 6 |
| 2018 | 56 | 0 | 6 |
| 2018 | 78 | 8 | 66575 |
Is there a way I could query or format the data so that I can have functional slicers for both month and year simultaneously?
Thank you and all the best!
Hello MiekeV
If you apply a couple steps to your query you can get what you are looking for.
1. Unpivot value columns
2. Remove "Amount" from the month name
3. Add a Month-Year column
I have attached my sample file for you to take a look at.
3 Replies
Hello MiekeV
If you apply a couple steps to your query you can get what you are looking for.
1. Unpivot value columns
2. Remove "Amount" from the month name
3. Add a Month-Year column
I have attached my sample file for you to take a look at.
Sorry it took a while to get back to this! The "unpivot" function worked perfectly!
2 new columns were created, one for the month and the other with the values.
Thank you!
I think, best is you transpose it in power bi using unpivot. Else you use union/summarize and create a new table merging all month in one.
