Forum Discussion
Solved
Extracting a Property from a Dictionary/JSon Object
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 perf...
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"
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}
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"