Forum Discussion

campelliann's avatar
campelliann
Post Patron
4 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

     

    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's avatar
Anonymous
Not applicable
4 years ago

 

 

 

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}