Power Query’s simple scalar values—like date, number and logical—can easily be converted to strings. Each has a corresponding type-specific “ToText” method (like Date.ToText or Number.ToText). The generic Text.From can also be used.
But what if you want to render a table, list or record textually? There is no built-in way to convert values of these types directly to text.
However, you can convert them to JSON…and then render that JSON as text!
(input as any) as nullable text =>
if input = null
then null
else Text.FromBinary(Json.FromValue(input))
Handy to render out a complex, nested structure so that you can see all of it at once!
Example
Here’s an example rendering out a table that contains a list of records:
let
AdvancedToText =
(input as any) as nullable text =>
if input = null
then null
else Text.FromBinary(Json.FromValue(input)),
Value =
#table(
{"ItemID", "Options"},
{
{123, { [Type = "Engraving", Cost = 15.99], [Type = "BonusPackage", Price = 100 ]} }
}
),
Result = AdvancedToText(Value)
in
Result
Outputs (pretty printed for clarity):
[{
"ItemID": 123,
"Options": [{
"Type": "Engraving",
"Cost": 15.99
}, {
"Type": "BonusPackage",
"Price": 100
}]
}]