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

Hi,

 

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

donsvensen's avatar
donsvensen
Icon for Skilled Sharer rankSkilled Sharer
10 years ago

Hi

 

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

 

/Erik

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

    Hello,

     

    Here they are:

    let
    Source = MySQL.Database("host.domain.com", "database", [ReturnSingleDatabase=true]),
    tablexxx = Source{[Schema="database",Item="tablexxx"]}[Data]
in
    database_tablexxx
    • donsvensen's avatar
      donsvensen
      Icon for Skilled Sharer rankSkilled Sharer
      10 years ago

      Hi again,

       

      You should modify the first line to this then

       

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

       

      and the bold+italic column name should refer to the field in your table

       

      /Erik