All text to the Right of Last occurrence of a -

[common763]

I would think this would be easy to plug into Co-Pilot but it isnt giving me what I want.  Need some help.  Co-Pilot is drafting a formula that isnt doing what I asked and was pretty concise in how I was asking, or at least I thought. 

I have a dataset pulled in from Salesforce that is a text column.  I need to insert a new column that will give me just the City, State if it is in the row, and if there is no - or City, State then blank.  Here are a couple scenarios to represent what I need.

111-222-333-Albany, NY CHANGED to Albany, NY

222-333-444-Syracuse, NY CHANGED to Syracuse, NY

asdfsdfsdfsdfsdf CHANGED "No occurence of - so blank"

Basically everytime a City And State is in the cell, return just everything from the first - from the right.  If there is no City State thhere is no - so blank.  Any help would be appreciated.  Thanks. 

[Anonymous]

Hi @common763,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to create a new column based on the selected data of other column. As @ryan_mayu has already responded to your query, kindly go through his response and check if it solves your issue.

I would also take a moment to thank @ryan_mayu and @KNP, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[ryan_mayu · Accepted solution]

@common763 

you can try this in PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ01DUyMtI1NjbWdcxJSsyr1FHwi1SK1YlWggmbmJjoBlcWJSaXFqfCJROLU9KQkVJsLAA=", 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}}),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "state", each Text.AfterDelimiter([Column1], ", "), type text),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text After Delimiter", "Country", each Text.BetweenDelimiters([Column1], "-", ",", 2, 0), type text)
in
#"Inserted Text Between Delimiters"

or try DAX to get the output

state2 =

var _p=find(",",[Column1],1,0)

return if (_p=0,blank(),mid([Column1],_p+1,100))

ExtractCity =

VAR FirstHyphenPos = SEARCH("-", [Column1], 1, BLANK())

VAR SecondHyphenPos = SEARCH("-", [Column1], FirstHyphenPos + 1, BLANK())

VAR ThirdHyphenPos = SEARCH("-", [Column1], SecondHyphenPos + 1, BLANK())

VAR CommaPos = SEARCH(",", [Column1], SecondHyphenPos + 1, BLANK())

VAR CityStartPos = ThirdHyphenPos + 1

VAR CityLength = CommaPos - CityStartPos

RETURN

IF(

    NOT(ISBLANK(FirstHyphenPos)) && NOT(ISBLANK(SecondHyphenPos)) && NOT(ISBLANK(CommaPos)),

    MID([Column1], CityStartPos, CityLength),

    BLANK()

)

pls see the attachment below

[common763]

thanks so much.  appreciated.

[ryan_mayu]

you are welcome

[KNP]

Do you want PowerQuery or DAX?