Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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.
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
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
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
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
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?
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |