How to use Date slicer with column for each month

[CharlesL]

I have a table that I am trying to filter using a date slicer.  The problem is that the table is set up with a month/year column for each month, and a row for each of over 300 sites, and an event count for each location during any given month. (example below)

I have a separate "Date" table that I am using as a Dim table, but I cannot create a relationship with 16 month columns.

I have thought of transposing the data into 3 columns, Site, Date, Quantity, then I can have a 1 to many relationship with the date.  The data transposition will mean writing a macro i guess which is possible, but hoping for a better way.  The database is updated monthly by adding a new column for the new month.

Any suggestions would be appreciated.

Site #	Jan	Feb	Mar	April	May	June	July	Aug	Sep	Oct	Nov	Dev	Jan	Feb	Mar	April	May	Total
438	2		1	3	2	5	2	3	2		1	2		4	1	2		30
523	2			2	1		1	1	2	1	2		1	1	2	1		17
784			1		1	1		3	1	3	1	1			3	2		17
792		2					1			3			1	1		1	1	10
800				1		1	1	3		1		1		1	1	1		11

[CharlesL]

Thanks for the suggestion, but it didnt work for me.  When I selected all the date columns, i lost the other two, and then it only unpivoted a single month at a time.

I basically need each site number to end up with 24 rows (one per month).  The first column would be site number, the second would have the date, and the third the number of occurances for that site, that month.

Not sure if there is an easy way in PQ to make that happen.

[Anonymous]

You need to make sure the date columns have proper dates in them and not just the month number because this will merge all months together even if they belong to different years. If your month columns only contain the month, you should replace it with the month starting date. As for the unpivot transformation, you are definitely doing something wrong for it not to work. Can you share the pbix and data source for us to further investigate? 

[CharlesL]

Let me mess with the dates, and try the transformation again...If I am still stuck, I will convert from MM/YYY to MM/DD/YYYY and see what happens...thx

[CharlesL]

While I was not able to get the suggestion to work, I was able to get with the team providing the data and have them provide access to the root data, vs the chart they were providing.  Direct source is always better, now I am ok with proceeding to provide the visualizations required.  

Thanks for your help.  I am still going to mess with the unpivoting to better understand how it works!

[Anonymous · Accepted solution]

Is this table in your Power BI model? If so, you do not need to write a macro. All you need to do is open query editor, select all the month/year columns, then click on "Unpivot Columns" as shown in the screenshot below. This will create a 3 column table as you mentioned and then you can create a relationship between the new date column and your date dim table.