Render Tables, Lists, Records -> Text

, ,

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!


Here’s an example rendering out a table that contains a list of records:

    AdvancedToText = 
        (input as any) as nullable text =>
            if input = null
            then null
            else Text.FromBinary(Json.FromValue(input)),
    Value = 
            {"ItemID", "Options"},
                {123, { [Type = "Engraving", Cost = 15.99], [Type = "BonusPackage", Price = 100 ]} }
    Result = AdvancedToText(Value)

Outputs (pretty printed for clarity):

	"ItemID": 123,
	"Options": [{
		"Type": "Engraving",
		"Cost": 15.99
	}, {
		"Type": "BonusPackage",
		"Price": 100

Leave a Reply

Your email address will not be published. Required fields are marked *