Forum Discussion
webportal
Impactful Individual
Impactful IndividualExtract 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...
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"
webportalImpactful Individual
Impactful IndividualIn the former case, there's no tax, so the field should be zero.
Eric_Zhang
Microsoft Employee
Microsoft Employee
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"