How to parse single line JSON and XML via Power Query?

Anonymous — Wed, 13 Mar 2024 20:16:38 GMT

Hello

I have the following flattened (in a single line) JSON:

{"credentials":{"personalAccessTokenName":"MY_TOKEN_NAME","personalAccessTokenSecret":"qlE1g9MMh9vbrjjg==:rZTHhPpP2tUW1kfn4tjg8","site":{"contentUrl":"MarketingTeam"}}}

I also have the following flattened (in a single line) XML:

Can you provide a sample Power Query how to convert these into JSON and XML documents that can be further queried via Power Query?

I tried the below but it does not seem to be able to parse it correctly:

Json.FromValue("{""credentials"":{""personalAccessTokenName"":""MY_TOKEN_NAME"",""personalAccessTokenSecret"":""qlE1g9MMh9vbrjjg==:rZTHhPpP2tUW1kfn4tjg8"",""site"":{""contentUrl"":""MarketingTeam""}}}")

Also, there seems there is no similar command for XML.

Thanks!

Re: How to parse single line JSON and XML via Power Query?

lbendlin — Thu, 14 Mar 2024 00:49:02 GMT

let Source = Json.Document("{""credentials"":{""personalAccessTokenName"":""MY_TOKEN_NAME"",""personalAccessTokenSecret"":""qlE1g9MMh9vbrjjg==:rZTHhPpP2tUW1kfn4tjg8"",""site"":{""contentUrl"":""MarketingTeam""}}}"), #"Converted to Table1" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table1", "Value", {"personalAccessTokenName", "personalAccessTokenSecret", "site"}, {"personalAccessTokenName", "personalAccessTokenSecret", "site"}), #"Expanded site" = Table.ExpandRecordColumn(#"Expanded Value", "site", {"contentUrl"}, {"contentUrl"}) in #"Expanded site"

let Source = Xml.Document("<tsRequest><credentials personalAccessTokenName=""MY_TOKEN_NAME"" personalAccessTokenSecret=""qlE1g9MMh9vbrjjg==:rZTHhPpP2tUW1kfn4tjg8""><site contentUrl=""MarketingTeam""/></credentials></tsRequest>"), #"Expanded Value" = Table.ExpandTableColumn(Source, "Value", {"Name", "Value"}, {"Name.1", "Value.1"}), #"Expanded Value.1" = Table.ExpandTableColumn(#"Expanded Value", "Value.1", {"Name", "Value"}, {"Name.2", "Value"}), #"Expanded Value1" = Table.ExpandTableColumn(#"Expanded Value.1", "Value", {"Name", "Value"}, {"Name.3", "Value.1"}) in #"Expanded Value1"

topic How to parse single line JSON and XML via Power Query? in Power Query

How to parse single line JSON and XML via Power Query?

Re: How to parse single line JSON and XML via Power Query?