Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingTry 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.