PowerApps: Format Table to JSON

Question

I want to convert my data from a table in PowerApps into JSON format.

Here's my Data:

Here's my current code:

Set(
 varJSONProductTagging, 
 JSON(
     <-Source->,
         "ProductCode",
         "Description",
         "KPICode",
         "DSLType"
     ),
     JSONFormat.IndentFour
 ));

Here's the result:

[{
    "ProductCode": "SD204",
    "Description": "Broadband Business SDSL 2Mb - Managed",
    "KPICode": "DSL3",
    "DSLType": "SDSL"
},
{
    "ProductCode": "SD219",
    "Description": "Broadband Business SDSL 2Mb - Wires Only",
    "KPICode": "DSL3",
    "DSLType": "SDSL"
},
{
    "ProductCode": "IOMDDI35",
    "Description": "ISDN DDI: Nobles Hospital - 650000"
}]

As you can see, on the 3rd set, KPICode and DSL Type are missing because they are null. What I want to do is to still include the columns with its value set to null. Like this:

[
{
    "ProductCode": "SD204",
    "Description": "Broadband Business SDSL 2Mb - Managed",
    "KPICode": "DSL3",
    "DSLType": "SDSL"
},
{
    "ProductCode": "SD219",
    "Description": "Broadband Business SDSL 2Mb - Wires Only",
    "KPICode": "DSL3",
    "DSLType": "SDSL"
},
{
    "ProductCode": "IOMDDI35",
    "Description": "ISDN DDI: Nobles Hospital - 650000"
    "KPICode": null,
    "DSLType": null
}

]

Any way I can do that in powerapps?

score 2 · Accepted Answer · answered Oct 21 '21 at 14:23

You can force the "undefined" fields to become a blank (null) value by using the ForAll function to force the fields to be defined. In this case, you would have something similar to this:

Set(
  varJSONProductTagging, 
  JSON(
    ForAll(
      ShowColumns(
        <-Source->,
        "ProductCode",
        "Description",
        "KPICode",
        "DSLType"),
      {
        ProductCode: ProductCode,
        Description: Description,
        KPICode: Coalesce(KPICode, Blank()),
        DSLType: Coalesce(DSLType, Blank())
      }
   ),
   JSONFormat.IndentFour
 ));

Notice that the Coalesce function does not differentiate between undefined, blank or empty strings. In this case, if one of the values of KPICode (or DSLType) is an empty text, it will be converted to null in the JSON output. If you don't want that to happen, you can be explicit in comparing to a blank value:

Set(
  varJSONProductTagging, 
  JSON(
    ForAll(
      ShowColumns(
        <-Source->,
        "ProductCode",
        "Description",
        "KPICode",
        "DSLType"),
      {
        ProductCode: ProductCode,
        Description: Description,
        KPICode: If(KPICode = Blank(), Blank(), KPICode),
        DSLType: If(DSLType = Blank(), Blank(), DSLType)
      }
   ),
   JSONFormat.IndentFour
 ));

PowerApps: Format Table to JSON

1 Answers1