Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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"

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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"

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.