Create New Column

[Anonymous]

Hi group, 

I am new to PBI and have a question wondering if any of you can help. 

I am trying to create a new formula in Power Query. There are existed columns: userid, digital property and personOID. The new formula I am trying to create will meet this condition: if ( digital property = "abc.com" ) then personoid else (right ( userid , strlen ( userid ) -strpos ( userid, '\' ) ) ) , for more details please see the screenshot I attached. I am thinking I should use a "custom formula", however as I am not that familiar with PBI language, my formula keeps getting error. Any thought is appreicated! Thanks. 

[edhans · Accepted solution]

Try this @Anonymous 

if [Digital Property] = "abc.com" then [PersonOID] else Text.AfterDelimiter([userID], "\")

Full code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxK1kvOz1XSUTI0MjYxBdJKsTrRSknJKVBhIMrMzyuOiXH0dQcqwZRNyknMiIkpzs9NLcnIzEtXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Digital Property" = _t, PersonOID = _t, userID = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Digital Property] = "abc.com" then [PersonOID] else Text.AfterDelimiter([userID], "\"))
in
    #"Added Custom"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

Thanks so much! This sure worked as I don't see synax errors. However I got this note "This step results in a query that is not supported in DirectQuery mode" so all the values under new column are null. Will you happen to know how we get this error fixed? Thanks in advance!

[edhans]

You cannot using Text.AfterDelimiter. You might try a combination of Text.Start and Text.PositionOf, but I am 99% sure Text.PositionOf doesn't fold, so that won't work. 

I would ask though why are you using direct query? That is not how Power BI is designed to work, and will usually result in slower performance for the end user. Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

It is tempting for SQL people to use it, but you shoudn't. It is basically uncached mode, and you bypass all of the performance and features of the Vertipaq engine.