Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Removing duplicates after changing date column from long format to yyyy-mm

I have a column in PowerBI in which I want to display dates in yyyy-mm format, a goal that I have with this column is to create a filter that allows a user to select the year-month combination for which it wants to see information.

 

Whenever I have my data formatted in "long format", I neatly get one option per possible date in filters and such (even though multiple rows may contain the same date). So when I choose a certain date I might get to see multiple rows. This is exactly what I want:

 

 

But when I change the "indeling" to yyyy-mm, suddenly Power BI will show multiple options for exactly the same yyyy-mm combinations. When I messed around with the filter, it seems that each unique row in my data now suddenly gets a seperate yyyy-mm option, despite being identical to others:

 

I don't really understand why this would happen, at first I thought it was because the yyyy-mm would introduce duplicates, but these duplicates were already present in long format where everything worked fine.

 

Can someone help me make this column in yyyy-mm format, where I only have 1 option per "yyyy-mm" combination? So e.g. there should be one option 2015-01 that shows all rows with MonthYear = 2015-01. Thank you in advance for your time

  • Hi Anonymous 

     

    It seems that you are only formatting the date column. Have you tried to create a new column that returns the Year - Month combo?

     

    Hope this helps you.

    Hugh

1 Reply

  • Hi Anonymous 

     

    It seems that you are only formatting the date column. Have you tried to create a new column that returns the Year - Month combo?

     

    Hope this helps you.

    Hugh