Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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:

<tsRequest><credentials personalAccessTokenName="MY_TOKEN_NAME" personalAccessTokenSecret="qlE1g9MMh9vbrjjg==:rZTHhPpP2tUW1kfn4tjg8"><site contentUrl="MarketingTeam"/></credentials></tsRequest>

 

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!

Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago
    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"

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    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"