Forum Discussion

webportal's avatar
webportal
Impactful Individual
10 years ago
Solved

Extract data from JSON field

I'm using Power BI desktop to connect to a MySQL database.

One of the fields contains data with the following structure:

 

a:1:{s:3:"IVA";O:8:"stdClass":3:{s:11:"tax_namekey";s:3:"IVA";s:8:"tax_rate";s:7:"0.23000";s:10:"tax_amount";d:25.07000000000000028421709430404007434844970703125;}}

 

I need to transform the data in a way that allows the extraction of the value of the tax amount. That is, I need to transform this column to: 25.07.

 

How can I do this? I tried splitting the column by semicolon, but since not all the columns have the same number of semicolons it didn't work.

 

Thanks in advance!

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    webportal

     

    Then add a IF statement.

     

    let
    Source = Table.FromRows({{1, "a:1:{s:3:""IVA"";O:8:""stdClass"":3:{s:11:""tax_namekey"";s:3:""IVA"";s:8:""tax_rate"";s:7:""0.23000"";s:10:""tax_amount"";d:25.07000000000000028421709430404007434844970703125;}}"},{1, "a:1:{s:3:""IVA"";O:8:""stdClass"":3:{s:11:""tax_namekey"";s:3:""IVA"";s:8:""tax_rate"";s:7:""0.23000"";s:10:""tax_amount"";"}},{"id", "text"}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.PositionOf([text],"d:")>0 then Number.FromText(Text.Range([text],Text.PositionOf([text],"d:")+2,5)) else 0)  

in
    #"Added Custom"

12 Replies

  • donsvensen's avatar
    donsvensen
    Skilled Sharer
    10 years ago

    Hi,

     

    One way is to use the Text functions in Power Query

     

     

    //find the position

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOf([JSON], "amount")),

    //use the position and offset it

        #"Added Custom1" = Table.AddColumn(#"Added Custom", "d", each Text.Range([JSON], [Custom]+10, 5))

     

    this requires a fixed length after the text "amount"

     

    /Erik

     

    • webportal's avatar
      webportal
      Impactful Individual
      10 years ago

      Hi,

       

      Thanks a lot for helping, but nothing happens after inserting that code in the advanced editor.

      • donsvensen's avatar
        donsvensen
        Skilled Sharer
        10 years ago

        Hi

         

        Can you share the lines of your query statement just before you want to calculate the taxamount ?

         

        /Erik

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    webportal

     

    The subject is a little misleading as that string is not in a valid json format, is that a typo? If JSON, you can extract value in MySQL end, check Searching and Modifying JSON Values.

     

    Anyway, if the prefix "d:" is fixed, use a sub-string funtion, namely Text.Range in power query to extract that value.

     

    let
    Source = Table.FromRows({{1, "a:1:{s:3:""IVA"";O:8:""stdClass"":3:{s:11:""tax_namekey"";s:3:""IVA"";s:8:""tax_rate"";s:7:""0.23000"";s:10:""tax_amount"";d:25.07000000000000028421709430404007434844970703125;}}"}},{"id", "text"}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Range([text],Text.PositionOf([text],"d:")+2,5))  

in
    #"Added Custom"

     

    • webportal's avatar
      webportal
      Impactful Individual
      10 years ago

      Thank you Eric_Zhang

       

      The query worked, but only for that specific row. All other rows are gone.

       

      :-)

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        10 years ago

        webportal

         

        So can you confirm that string is a valid JSON?

         

        If not and all the rows in your case don't have certain patterns(eg: a "d:" prefix before the number), then I think there's almost no way to achieve your requirement.

         

        Can you be more specific about you scenario, what are the rest rows like?