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.
I have the following data that needs some conditional formating with an M Function:
I would like to make the following transformation:
In [model], extract the text before the first "space". If the [brand] is "BMW" and the extracted text is 4 characters long, then retunr just the first of those characters.
model = if [brand] = "BMW" and Text.Length([model]) = 4 then each Text.Start( Text.BeforeDelimiter([model], " "), 1) else each Text.BeforeDelimiter([model], " ")
For example from above, the first line of [model] would become "3", but the fourth would become "Santa"
Thanks for your help
Solved! Go to Solution.
Hi @xonder
Download example PBIX file with the following data and code.
Starting with this sample data
add a Custom Column with this line of code
if [brand] = "BMW" and Text.Length(Text.Start([model], Text.PositionOf([model], " "))) = 4
then Text.Start([model], 1)
else Text.Start([model], Text.PositionOf([model], " "))
to give this result
Note that in row 7 the result is X5 - is that what you want?
Also, you gave the example that row 4 would yield the result Santa so fromthat I've inferred that you wanted the first word returned if it'snot a a BMW and the first word in the model isn't 4 characters long.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi @xonder
Download example PBIX file with the following data and code.
Starting with this sample data
add a Custom Column with this line of code
if [brand] = "BMW" and Text.Length(Text.Start([model], Text.PositionOf([model], " "))) = 4
then Text.Start([model], 1)
else Text.Start([model], Text.PositionOf([model], " "))
to give this result
Note that in row 7 the result is X5 - is that what you want?
Also, you gave the example that row 4 would yield the result Santa so fromthat I've inferred that you wanted the first word returned if it'snot a a BMW and the first word in the model isn't 4 characters long.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
You can do it in steps.
Duplicate the model column (so that we keep the original model column for comparison).
Split the new column on the left most space.
Add a new column with code like this:
if [brand] = "BMW" and Text.Length([#"model - Copy.1"]) = 4 then Text.Start([#"model - Copy.1"],1) else [#"model - Copy.1"]
Covering 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.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |