Forum Discussion
Post ProdigyAdd custom column in Direct Query
Hi,
I have a table that using Direct Query. A column contain string of Month name that I need to manipulate a bit.
Something like this :
I need to change this to 3 digit string of month and then the year. So the result is like 'Feb 2012' or 'Aug 2012'
How can I achieve this in Direct Query ?
Thanks in advance
Hi all,
Found a solution ! and GOD BLESS THIS MAN -> Month Labels in Direct Query - Power BI - YouTube
So I created Calculated Column like this =
Month =VAR MonthNum = MONTH('Date'[Date])VAR YearNum = YEAR('Date'[Date])RETURNMID("xxJanFebMarAprMayJunJulAugSepOctNovDec", MonthNum * 3, 3) & " " & YearNum
Rgds,,
5 Replies
- edhans
Community Champion
You cannot. Unfortunately the Text.Start/Middle/End() functions will not fold, and therefore will not work with Direct Query.
YOu will need to work with your DBA to create a view for you that has this column, or provide the entire SQL statement and put it in the Advanced Options/SQL Statement, but I recommend going the View route.
admin_xlsiorHi edhans
Alright Thanks. I was hoping if there is some workaround. It looks like there is none 🙂
Thanks again
- mahoneypat
Microsoft Employee
If you can create a date column from that column with Direct Query, you could then do a custom format string on the DAX side with "MMM yyyy".
Creating a simpler and chart-friendly Date table in Power BI - SQLBI
Regards,
Pat
- admin_xlsior
Hi mahoneypat
It looks tempting, but both FORMAT and ADDCOLUMNS is not supprted by Direct Query. Am I right to say that ?
It does give me some idea of using Date.ToText in QueryEditor but still it's force me to switch to Import Mode.
Thanks,
- admin_xlsior
Hi all,
Found a solution ! and GOD BLESS THIS MAN -> Month Labels in Direct Query - Power BI - YouTube
So I created Calculated Column like this =
Month =VAR MonthNum = MONTH('Date'[Date])VAR YearNum = YEAR('Date'[Date])RETURNMID("xxJanFebMarAprMayJunJulAugSepOctNovDec", MonthNum * 3, 3) & " " & YearNum
Rgds,,
