Use existing columns to create JSON data with Power Fx formula in MS Dataverse

How to create valid JSON data from existing colums in .

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\"}"