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"
donsvensen's avatar
donsvensen
Icon for Skilled Sharer rankSkilled 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
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