Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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?
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
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
2 | |
2 | |
1 | |
1 |