Add custom column in Direct Query

[admin_xlsior]

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

[mahoneypat]

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 · Accepted solution]

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])

RETURN

MID("xxJanFebMarAprMayJunJulAugSepOctNovDec", MonthNum * 3, 3) & " " & YearNum

Rgds,,

[edhans]

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_xlsior]

Hi @edhans 

Alright Thanks. I was hoping if there is some workaround. It looks like there is none 🙂

Thanks again