Create JSON data with Power Fx formula column in MS Dataverse

How to create valid JSON data from existing columns in Dataverse?

Power Pages use Dataverse as a data store. Standard type tables in Dataverse lack JSON support. Sometimes you would need to use JSON data to ease out some operations, for example the one I explained in this post Dataverse table with 2 lookup and 1 formula columns.

What is a formula column

Formula columns are columns that display a calculated value in a Microsoft Dataverse table. Formulas use Power Fx, a powerful but human-friendly programming language. Build a formula in a Dataverse formula column the same way you would build a formula in Microsoft Excel. As you type, Intellisense suggests functions and syntax, and even helps you fix errors.

Formula columns have many limitations, eg:

Date time columns and date time functions UTCNow(), Now() can't be passed as a parameter to string functions.

How

1. In Dataverse table create a column with Data type -> Formula
2. Use the following code in the Formula field:

Concatenate(
    "{", 
        """sid"":","""",'sup Id', """,",
        """name"":","""",'sup Name', """,",
        """vat"":","""",'sup VAT', """,",
        """addr"":","""",'sup Address', """",
    "}"
)

sup Id, sup Name, sup VAT and sup Address are other columns from the same table where you create a formula column with JSON data.

Result in a table

{"sid":"sid-100001","name":"Another Custom Entity AS","vat":"no234344","addr":"Norway 2004"}

Result when quering the data with Web Api

"{\"sid\":\"sid-100001\",\"name\":\"Another Custom Entity AS\",\"vat\":\"no234344\",\"addr\":\"Norway 2004\"}"