cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alex_Mgg Regular Visitor
Regular Visitor

Convert 01/01 dates to whole month dates

Hi,

 

I have a date filter created in a spreadsheet which I am using to connect multiple data sets together through one date slicer in pbix. In the spreadsheet, the order, for example, goes:

1 2017

2 2017

3 2017

 

However in Power Query it recognises the values as:

01/01/2017

01/02/2017

01/03/2017

 

When visualising data, this therefore displays data on the first day of the month, and none of the of the other days within that month.

Is there a way to convert the column of dates within Power Query, maybe via text, to just display jan-2017, feb-2017 so it is a sum of the values as a whole for that month?

 

Is this possible?

 

Thanks

7 REPLIES 7
jthomson New Contributor
New Contributor

Re: Convert 01/01 dates to whole month dates

Which visualisation are you using that's having this? Can you not remove the option for days on the date hierarchy and get the results you want?

Smauro Regular Visitor
Regular Visitor

Re: Convert 01/01 dates to whole month dates

In Power BI Desktop, if you go to Data, select the column with your date and on Modeling->Format->Date Time you may change the appearance to what you like best. My guess is that "MMMM yyyy" would do it.

Alex_Mgg Regular Visitor
Regular Visitor

Re: Convert 01/01 dates to whole month dates

Hi - My format is already displaying this in data.

 

I should have made it clear, in 'edit queries' where the raw data is extracted, it is displaying 01/01/2016 etc. I need to convert the data here to a mmmm/yyyy as a whole

Microsoft v-jiascu-msft
Microsoft

Re: Convert 01/01 dates to whole month dates

Hi @Alex_Mgg,

 

The format could be easy. The point is creating a proper relationship. For example.

Table 1                 Table2

mmyy                   ddmmyy        Solution

01-2017               01-01-2017    01-2017

02-2017               02-01-2017    01-2017

03-2017               03-01-2017    01-2017

  ... ...                     01-02-2017    02-2017

                             ... ....               ... ....

Then the relationship will be created between mmyy and Solution.

BTW, you should store mm/yyyy as TEXT due to they are not dates.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Smauro Regular Visitor
Regular Visitor

Re: Convert 01/01 dates to whole month dates

Hey @Alex_Mgg. I'm not sure I understand then what you're trying to do. If you're visualising data, how it is displayed in Query View doesn't really matter. If you correct how it's shown in Data, then the visualisations, granted that you don't choose the date hierarchy would look like that:
2017-12-22 09_45_58-SQLBuilt_MonthlyMetrics - Power BI Desktop.png choosing a continuous axis or like that:
2017-12-22 09_48_11-SQLBuilt_MonthlyMetrics - Power BI Desktop.pngchoosing a categorical axis.


If you still want to do that, I think that @v-jiascu-msft's solution is the way to go, since it's actually going to let you create some graphs with this display mode (Were you to order his 'Table2'[Solution] by 'Table2'[ddmmyy].

The query mode to actually create this column would be to extract the year in a new column using Date.Year, extract the month in a new column using Date.Month, transform them to text afterwards (if you try to do it on the same line you'll get an error) and then combine then with a simple delimiter of your choice.

I hope that helps

EDIT: I don't think you'll need another table. Simply creating this new column in query editor and then choosing from Data "Sort by Column" <-your date column should do it

Microsoft v-jiascu-msft
Microsoft

Re: Convert 01/01 dates to whole month dates

Hi @Alex_Mgg,

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Alex_Mgg Regular Visitor
Regular Visitor

Re: Convert 01/01 dates to whole month dates

Hi,


Apologies for the late reply, its been a busy holiday period. I'm quite new to Power Query so any help with following those steps would be great! As a recap my table in Data is displaying on the left image and my Query Editor is displaying on the right image:

 

 

Data Table.PNGQuery Editor.PNG

 

I just need steps to get the query editor column (right image) into a solution column as the visuals are still pulling through data from the first day of the month even though the table in 'data' (left image) is encompassing the date as a whole.


Thanks,

Alex

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors