Data Formatting for Slicer

[MiekeV]

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!

[amitchandak]

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.

[jdbuchanan71 · Accepted solution]

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.

[MiekeV]

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!