Extracting a Property from a Dictionary/JSon Object

[campelliann]

Hi there,

So I have a table which is giving some performance issues. Each row has a column with a huge dictionary {property1: value, property2: value2....}

Which is the best way in term of performance to extract only "property n". Extract between delimiters?

Thanks

[campelliann]

Hi there, 

Its a string, I call dicticionary as in Python. I went with extract from delimiters for getting "ticketstatusid" property. The table has now 30.000 rows /10 columns ( I can filter to 3000 rows), but it will be increasing everyday, perhaps 100 new records/rows per day


{"ApplicationId":2,"AssignDate":null,"AssignUserId":null,"ClientId":2,"ClosedDate":"2020-07-07T13:08:00","ClosedUserId":"FDeeeeeeeeeeee","CreateDate":"2020-07-01T18:50:00","CreatedBy":"28fffffffffff","DatePro":null,"DateQua":null,"Description":"The error is this....","TicketStatusId":7,"TicketTypeId":1,"TicketUrgencyId":2,"UpdateDate":"2021-06-30T19:09:25.4152367+01:00","UpdatedBy":"FD9B4}

[Anonymous · Accepted solution]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZHNboMwEIRfBfnaENkQws+tCYrUW6uaU8iBwhasuoBsc0BV372LkzhRuvJcPs+Odu3jkVRkRT5QNapB/ZTkeRylqCsjhv6lKUkWrJBpLdo+rwwg6CcpHSs0KGu70L0U0BvXuJeDhubSWJKABtSnMR7OwowmGaUlcTaXVZJDDnd19ijAmP9RjLMki6iLsrZmN59dyeet7P0S8KqG28QLeJuqOwC6VmJcHsBm8A48UGpQntCe6YReY9ksLuovMO+mMpO2g8cO8nkEi5hDhWqhr2f3NsXYPCzEfLr1Q8pZmtE0C6L1hkVBuI2fKLuud266rnfI093mF/+tRXUogZKob3I6/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t, Colonna3 = _t, Colonna4 = _t, Colonna5 = _t, Colonna6 = _t, Colonna7 = _t, Colonna8 = _t, Colonna9 = _t, Colonna10 = _t]),
    tr=Table.Repeat(Origine,30000),
    
    #"Aggiunta colonna personalizzata" = Table.AddColumn(tr, "split", each Text.Split(List.FindText(Text.Split([Colonna5],","),"StatusId"){0},":"){1})
in
    #"Aggiunta colonna personalizzata"

try this.

check if the excution time is satisfying

or this which is faster, if you know the position of you wanted property

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZHNboMwEIRfBfnaENkQws+tCYrUW6uaU8iBwhasuoBsc0BV372LkzhRuvJcPs+Odu3jkVRkRT5QNapB/ZTkeRylqCsjhv6lKUkWrJBpLdo+rwwg6CcpHSs0KGu70L0U0BvXuJeDhubSWJKABtSnMR7OwowmGaUlcTaXVZJDDnd19ijAmP9RjLMki6iLsrZmN59dyeet7P0S8KqG28QLeJuqOwC6VmJcHsBm8A48UGpQntCe6YReY9ksLuovMO+mMpO2g8cO8nkEi5hDhWqhr2f3NsXYPCzEfLr1Q8pZmtE0C6L1hkVBuI2fKLuud266rnfI093mF/+tRXUogZKob3I6/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t, Colonna3 = _t, Colonna4 = _t, Colonna5 = _t, Colonna6 = _t, Colonna7 = _t, Colonna8 = _t, Colonna9 = _t, Colonna10 = _t]),
    tr=Table.Repeat(Origine,30000),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(tr, "split", each Text.Split([Colonna5],","){11})
in
    #"Aggiunta colonna personalizzata"

[Anonymous]

the dictionary you are talking about is a long string of text, not a dictionary in the sense of power query?

Can you post an example with a couple of dummy rows and columns that reproduce your situation in terms of sizes and data types?
Also add the actual dimensions of your table.

PS

You could try with Text.Split ([column], ",") {n-1}