Forum Discussion

webportal's avatar
webportal
Icon for Impactful Individual rankImpactful 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_ra...
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"
webportal's avatar
webportal
Icon for Impactful Individual rankImpactful 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
Icon for Microsoft Employee rankMicrosoft 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?

  • webportal's avatar
    webportal
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    OK, I see.

     

    Well, some rows have that pattern while others have not.

    They can either be:

    a:1:{s:10:"VAT Exempt";O:8:"stdClass":3:{s:11:"tax_namekey";s:10:"VAT Exempt";s:8:"tax_rate";s:7:"0.00000";s:10:"tax_amount";i:0;}}

     

    Or:

    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:12.6500000000000003552713678800500929355621337890625;}}

     

    If it's impossible to grab the tax amount, it's ok, I understand this is a complicated structure.


    Thanks for your help!

    • Eric_Zhang's avatar
      Eric_Zhang
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago
      webportal wrote:

      OK, I see.

       

      Well, some rows have that pattern while others have not.

      They can either be:

      a:1:{s:10:"VAT Exempt";O:8:"stdClass":3:{s:11:"tax_namekey";s:10:"VAT Exempt";s:8:"tax_rate";s:7:"0.00000";s:10:"tax_amount";i:0;}}

       

      Or:

      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:12.6500000000000003552713678800500929355621337890625;}}

       

      If it's impossible to grab the tax amount, it's ok, I understand this is a complicated structure.


      Thanks for your help!

       

      The solution in my previous reply should work with the latter pattern in your reply. What value is expected from the former case?

      • webportal's avatar
        webportal
        Icon for Impactful Individual rankImpactful Individual
        10 years ago

        In the former case, there's no tax, so the field should be zero.