M query Search for text in between other text

[msantillan]

I have a column which has a values:

BAU_MNL, BAU_REPORTING_MNL

BAU_CHANGEREQUEST_MNL, INV_COLLEGE_MNL

BAU_MNL, INV_COLLEGE_MNL, BAU_CAPACITY_MNL

I am trying to get the values that only has "BAU_" and "_MNL" (The bold ones). When I used this code, it returned a complex value:

Text.BetweenDelimiters([Labels], (if [Label Type]="BAU" then "BAU_" else if [Label Type]="BUG" then "BUG_" else if [Label Type]="ENH" then "ENH_" else null), "_MNL")

This is what it returned for the first row:

_MNL, BAU_REPORTING

This is the correct output:

REPORTING

CHANGEREQUEST

CAPACITY

Anyone has any particular solution for this? Thank you.

[mahoneypat · Accepted solution]

Here is one way to do it in query with List functions.  Please paste the below M code into a new blank query (click on Advanced Editor and replace the text there with this text).

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnIMjff189FRADGCXAP8g0I8/dxBQkqxOhBpZw9HP3fXINfAUNfgEIhiT7+weGd/Hx9Xd1cUpdgkIUY7OwY4OnuGREKUxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",",","",Replacer.ReplaceText,{"Text"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.RemoveItems(List.FindText(List.FindText(Text.Split([Text], " "),"BAU_"),"_MNL"),{"BAU_MNL"})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.BetweenDelimiters(Text.Combine([Custom], ","), "BAU_", "_MNL")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type text}})
in
#"Changed Type1"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

Here is one way to do it in query with List functions.  Please paste the below M code into a new blank query (click on Advanced Editor and replace the text there with this text).

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnIMjff189FRADGCXAP8g0I8/dxBQkqxOhBpZw9HP3fXINfAUNfgEIhiT7+weGd/Hx9Xd1cUpdgkIUY7OwY4OnuGREKUxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",",","",Replacer.ReplaceText,{"Text"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.RemoveItems(List.FindText(List.FindText(Text.Split([Text], " "),"BAU_"),"_MNL"),{"BAU_MNL"})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.BetweenDelimiters(Text.Combine([Custom], ","), "BAU_", "_MNL")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type text}})
in
#"Changed Type1"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AlB · Accepted solution]

Hi @msantillan 

Another solution, although @mahoneypat's is more elegant

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnIMjff189FRADGCXAP8g0I8/dxBQkqxOhBpZw9HP3fXINfAUNfgEIhiT7+weGd/Hx9Xd1cUpdgkIUY7OwY4OnuGREKUxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1], ", ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each let firstPos_ = Text.PositionOf([Custom],"BAU_"), secondPos_ = Text.PositionOf([Custom],"_MNL"), res_ = if firstPos_>= 0 and secondPos_>=0 then Text.Middle([Custom],firstPos_+4,List.Max({secondPos_-(firstPos_+4),0})) else null in res_),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] <> null and [Custom.1] <> "")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers