cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Create New Column

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. Capture.PNG

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Try this @Anonymous 

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

edhans_0-1637691393834.png

 

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.

 



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

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

Try this @Anonymous 

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

edhans_0-1637691393834.png

 

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.

 



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
Anonymous
Not applicable

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!

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.

 

 



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

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!