Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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"
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"
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?
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!
@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?
In the former case, there's no tax, so the field should be zero.
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"
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
Hi,
Thanks a lot for helping, but nothing happens after inserting that code in the advanced editor.
Hi
Can you share the lines of your query statement just before you want to calculate the taxamount ?
/Erik
Hello,
Here they are:
let Source = MySQL.Database("host.domain.com", "database", [ReturnSingleDatabase=true]), tablexxx = Source{[Schema="database",Item="tablexxx"]}[Data] in database_tablexxx
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |