Getting data from a string (json like structure)

[pforchito]

Hi Guys... I'm facing a problem to get a string from a field... the string is like a json structure:

{'id': 3489, 'name': 'Cesar Souza', 'picture_url': 'xpto.com/img.jpg', 'role': 'test'}

I need to get the name param...

[PhilipTreacy]

Hi @pforchito 

Download this PBIX file with a working solution.

You say it is JSON like because it's not actually valid JSON?

This query code will work.  Paste it into a blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo4pNTAwMs9MgdBWCsYmFpY6ChBeXmJuKkwcQjunFicWKQTnl1YlQgRgSgsyk0tKi1LjS4tyUHVUFJTk6yXn5+pn5qbrZRWko2orys9Bs6EktbgEwqpVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [json_string = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"json_string", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",  each Text.Remove(Text.Split(Text.Split([json_string],","){1},":"){1},{"'"}))
in
    #"Added Custom"

Regards

Phil

[v-deddai1-msft]

Hi @pforchito ，

If you are getting data from json file, here is a link with detailed steps for your reference: https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[parry2k]

@pforchito assuming it is in a file, you can use text/csv as file source and transform json to get the value of the name column. What have you tried so far?