Extract data from JSON field

[webportal]

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!

[Eric_Zhang]

@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]

Thank you @Eric_Zhang

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

🙂

[Eric_Zhang]

@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]

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]

---

@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]

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

[Eric_Zhang · Accepted solution]

@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]

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]

Hi,

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

[donsvensen]

Hi

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

/Erik

[webportal]

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]

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