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
}]
}]
Hi Ben,
Thanks for the post.
Do you have a neat solution to handle cases when values not compatible with the JSON conversion function (like errors, types and func defs) are present in the input structure?
Also is there an eady way to generate the pretty print with M?
Best regards,
Dimitar
Hi Dimitar! Off the top of my head, the idea that comes is to have a recursive method that checks its input value by value (e.g. each column value, if a table; each list item value, if a list; etc.) and replaces the incompatible values with text placeholders (e.g. if an error is present, perhaps replace the error with the text “error”).