Find Last Code

[NPC]

Hi All,

Wondering if you can help.

Basically I’m trying to write a query that is going to pull specific codes (numbers) from a column that is mostly text (comments). The challenge is that the same or a few other codes can be present within that column and the only one I need is the one that appears last. Here is an example of what a column named “Comments” can look like :

“-------------------- FSA This inspection needs to be completed within the next 12 months. Code 004-22. The issue is not urgent Reviewed by xxxx ---------------------------------------- FSA After reviewing this record, this inspection needs to be completed in 3 months. Recomending 002-22. Reviewed by yyyy.”

In this case, I would want my query find “002-22 ”. 

There is no particular style/format that folks are entering their comments other than the codes.

Is there a way I can have the query read from right to left or bottom to top so I can only pull the last code.

Thank you for your help.

[hnguy71 · Accepted solution]

@NPC ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY7BDsIgEER/ZcJZTIFqwtk/MN5IDwpri6aQFFr17wV0k93D7L6dMYadoiN0Xc+l3OMyEXxKa50IMWNdRgoZZ9o8vcjh9sG7VBFsnCk4H8YCywqzYWeYkIr3hyOuyd1rQ0nBpWq7zUbtn48ErTXXuml5KkYxUDGDLUma2EBRn8JF+gWZifL/YvgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

    // Source: https://www.excel-first.com/pattern-matching-in-power-query/
    // what is your current pattern.
    Pattern = "***-**",

    // length of pattern
    pLength = 6,

    // valid characters to look for
    ValidCharacters = List.Transform({0..9}, each Text.From(_)),

    // replace valid chars with pattern key
    ReplaceValidWithPatternKey = List.Zip({ValidCharacters,List.Repeat({"*"},List.Count(ValidCharacters))}),

    // find pattern. return last pattern.
    LastCode = 
        Table.AddColumn(
            Source, 
            "LastCode", 
            (x) => 
                Text.End(
                    Text.Combine(
                        List.Transform( 
                            Text.PositionOf( 
                                Text.Combine( 
                                    List.Transform( 
                                        List.ReplaceMatchingItems( 
                                            Text.ToList( x[Column1]), ReplaceValidWithPatternKey
                                            ), 
                                            Text.From
                                        )
                                    ), 
                                Pattern , Occurrence.All
                            ), 
                    each Text.Range(x[Column1],_, pLength )
                        ), ","
                ),
                pLength
            ),
            type text
        )
in
    LastCode

[NPC]

Thanks @amitchandak. Still trying to read through what you suggested. 

[amitchandak]

@NPC , try to use text analytics.if that can help https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-ai-insights