Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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"
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"