Using Power Query Extract Name Values

Power Query
1

Updated query

Hi @Melissa,

Thank you for taking the time to look on this. I have attached a sample pbix file.

My goal is to create a new column that extracts the names from the data, such as “John Legend” and “Ed Sheeran”. etc.

Sample.pbix (23.5 KB)

2

Hi @ronald_balza,

This syntax is invalid or no M code…

If you are unable to share an actual example please refer to this section: Item, Field Access, Lookup, Selection and Projection in the Power Query/M - Nested Structures course.

1 Like
3

Hi @ronald_balza,

Give something like this a go

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiq5+1DAnuCQxLe1Rw1wrMM/TBcQEMkwMTEzNgWwdINsvMTcVKuyVn5Gn4JOanpqXAhSp1UHRY2pmamhojKnJNUUhOCM1tSgxD6QntjY2Ji8mTylWB78LDI2MTUwxDXMpTVTwySxIBBtFlEHGpkYmWLwSkliZk1+kEFyemVaC6RegJqzWh2dkluSlVip45JcWl+SjeigWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Staff Name" = _t]),
    GetNames = Table.AddColumn(Source, "Names", each 
        [
            s = Splitter.SplitTextByCharacterTransition( {"A".."z", " "}, (x)=> not List.Contains( {"A".."z", " "}, x))([Staff Name]),
            n = List.PositionOf( List.Transform(s, (y)=> Text.End(y, 4)), "Name", Occurrence.All ),
            r = List.Transform( n, (v)=> Text.Remove( s{v+1}, {"”",":", "“"} ))
        ][r]
    )
in
    GetNames

Amend to your needs.
I hope this is helpful

3 Likes
4

Hi Ronald,

This is my way to solve this issue:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiq5+1DAnuCQxLe1Rw1wrMM/TBcQEMkwMTEzNgWwdINsvMTcVKuyVn5Gn4JOanpqXAhSp1UHRY2pmamhojKnJNUUhOCM1tSgxD6QntjY2Ji8mTylWB78LDI2MTUwxDXMpTVTwySxIBBtFlEHGpkYmWLwSkliZk1+kEFyemVaC6RegJqzWh2dkluSlVip45JcWl+SjeigWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Staff Name" = _t]),

        Names = Table.AddColumn(Source, "Custom", each List.RemoveItems(List.Transform({0..20},(x)=> Text.BetweenDelimiters([Staff Name],",“Name”:“","”",x)),{""}))
in
    Names

I tried to use simple and short code but instead of the number 20, you can put a count of the maximum name in the text.

2 Likes