cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Add 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 :

image.png

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

1 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,,
 

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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,

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,,
 

View solution in original post

Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @edhans 

 

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

 

Thanks again

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors