New column in direct query Right function

[miltenburger]

Hi all,

I used DirectQuery to get data from a table out of my Kaseya Database.

One of the columns called User contains the following information:

abcdefgh\a.klaassen

abcdefgh\b.bass

abcdefgh\c.curry

I want to create a new column which contains the following information:

a.klaassen

b.bass

c.curry

So basically i want everything that is right from the \ in my column User.

Any idea how to do this?

[vanessafvg]

@miltenburger

=REPLACE('table'[fieldname],1,9,"")  

https://msdn.microsoft.com/en-us/library/ee634374.aspx

[miltenburger]

@vanessafvg

Thanks for your fast assist,

but the REPLACE function is not working when adding a new column....

only when i choose the option new measure, but that is not what i want/ and even not works

[vanessafvg]

@miltenburger works fine for me what are you doing?

Column = REPLACE(Dates[MonthName],1,3,"")

I took the column monthname and removed the first 3 digits rom january into a new column

[miltenburger]

As @Greg_Deckler mentioned, it's a live connection, which means not all of the DAX functions work

And REPLACE won't work in this case

[v-shex-msft]

Hi @miltenburger,

Maybe you can try to write a measure to instead, there are lots of limitations when you use calculated column in directquery mode.

BTW, you can also try to add custom column in power query with these operations, but current these function seems not available in directquery mode.

Regards,

Xiaoxin Sheng

[miltenburger]

Hi @v-shex-msft,

Thanks for your answer,

Do you have any idea to write a measure for this? Because i really don't no how to fix this in liveconnection

Thanks a lot

[v-shex-msft]

HI @miltenburger,

I'm not so sure if below measure formula can works in live mode, maybe you can try it:

Right Part =
VAR curretn_item =
    LASTNONBLANK ( Table[Column], [Column] )
RETURN
    PATHITEM ( SUBSTITUTE ( curretn_item, "\", "|" ), 2 )

Regards,

Xiaoxin Sheng

[vanessafvg]

@miltenburger are you not able to create it in the source data? personally i dont know how you would do that if you doing it in a live connection.   when using live connection power bi expect you do do most of the modelling in the source data.

[Greg_Deckler]

It's probably an issue that it is a Live connection and not import. This is why FIND and SEARCH do not work.