Forum Discussion
IF conditional m language
I'm currently trying to do a conditional for a column called Item Text. If any rows in that column start with the characters "ELE", then remove the first 24 characters, else do nothing. So far I have this but I'm not sure if im correct syntax wise or what to put in the else statement.
let
Custom1 = if Text.StartsWith([Item Text], "ELE") then Splitter.SplitTextByPositions({24}, {"Item Text"}) else
in
Custom1
Check the following code on a blank query:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvVxLSxPLSqpLM3KSE9LKU6sSC5LysrLzswH85VidTDUgMUcHR0J6nNKAipBNiHYyNDIEJeGWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Text" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([Item Text], "ELE") then Text.Middle([Item Text], 25, Text.Length([Item Text])) else "") in #"Added Custom"________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Hi alexpork
Starting from the table where you have the Item text column, create a custom column by:
1. Selecting Addcolumn --> Custom column in the menu of the Query editor (https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-custom-column)
2. Enter this code for the column when prompted:
= if Text.StartsWith([Item Text], "ELE") then Text.Middle([Item Text], 24, Text.Length([Item Text])-24 )) else [Item Text]
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
5 Replies
Hi alexpork
Starting from the table where you have the Item text column, create a custom column by:
1. Selecting Addcolumn --> Custom column in the menu of the Query editor (https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-custom-column)
2. Enter this code for the column when prompted:
= if Text.StartsWith([Item Text], "ELE") then Text.Middle([Item Text], 24, Text.Length([Item Text])-24 )) else [Item Text]
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Here's a better look into what I have so far based on your reply! I think the answer is close. No syntax errors were detected. However, I receive an error that says the field 'Item Text' of the record wasn't found.
I'm thinking that the column names wasn't able to be find somehow but I promoted the headers in the previous step so I'm not sure if this is why the error is occuring!
That means that the step test_Sheet doesn't have a column named [Item text].
Why are you adding the custom column to test_Sheet and not to #"Changed Type", the immediately previous step??
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Check the following code on a blank query:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvVxLSxPLSqpLM3KSE9LKU6sSC5LysrLzswH85VidTDUgMUcHR0J6nNKAipBNiHYyNDIEJeGWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Text" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([Item Text], "ELE") then Text.Middle([Item Text], 25, Text.Length([Item Text])) else "") in #"Added Custom"________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
