Convert 01/01 dates to whole month dates

[Anonymous]

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

[v-jiascu-msft]

Hi @Anonymous,

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

[Smauro]

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.

[Anonymous]

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

[Smauro]

Hey @Anonymous. 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:
 choosing a continuous axis or like that:
choosing 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

[Anonymous]

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:

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

[v-jiascu-msft]

Hi @Anonymous,

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

[jthomson]

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?