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 performance to extract only "property n". Extract between delimiters?

Thanks

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"

3 Replies

  • 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}

  • campelliann's avatar
    campelliann
    Post Patron
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      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"