IF conditional m language

[alexpork]

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

[Fowmy · Accepted solution]

@alexpork 

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 🙂

YouTube  LinkedIn

[AlB · Accepted solution]

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 

[alexpork]

@AlB @Fowmy 

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!

[AlB]

@alexpork 

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 

[alexpork]

@AlB 

I am still new to M language and still adjusting to the syntax. Once I added your suggestion in, that fixed the issue. Thank you so much!

[Fowmy · Accepted solution]

@alexpork 

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 🙂

YouTube  LinkedIn