arraysjsonpower-automate

Extract Unique Values from a Power BI JSON in Power Automate

I have the following JSON that comes from PowerBI (via the Power Automate button function)

[
  {
    "entity": {
      "Power BI values": [
        {
          "G-DATEMO": 4,
          "G-DATEQU": 2,
          "G-DATEYR": 2024,
          "G-PRODUCT": "Biscuit",
          "G-BAKERY": "West",
          "Year Quarter Name": "2024 Q2"
        },
        {
          "G-DATEMO": 4,
          "G-DATEQU": 2,
          "G-DATEYR": 2024,
          "G-PRODUCT": "Bun",
          "G-BAKERY": "West",
          "Year Quarter Name": "2024 Q2"
        },
        {
          "G-DATEMO": 4,
          "G-DATEQU": 2,
          "G-DATEYR": 2024,
          "G-PRODUCT": "Bun",
          "G-BAKERY": "South",
          "Year Quarter Name": "2024 Q2"
        },
      ]
    }
  }
]

I want to extract only the UNIQUE items for G-PRODUCT and G-BAKERY listed if its possible in separate arrays

I have tried applying a "apply to each" in side another "apply to each" but just end up with the array being full of 'Apply_to_each_2"

Need to achieve this inside of Power Automate

Solution

  • I saved your data into an array variable:

    save sample data

    The next step is to create a parse json action which will describe the structure of your json that was provided. In the content, I put the sample data array that I created. The schema should look like this:

    parse json action

    {
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "entity": {
                "type": "object",
                "properties": {
                    "Power BI values": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "G-DATEMO": {
                                    "type": "integer"
                                },
                                "G-DATEQU": {
                                    "type": "integer"
                                },
                                "G-DATEYR": {
                                    "type": "integer"
                                },
                                "G-PRODUCT": {
                                    "type": "string"
                                },
                                "G-BAKERY": {
                                    "type": "string"
                                },
                                "Year Quarter Name": {
                                    "type": "string"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

    Next, you need to create two select actions to extract the correct values for each item. it should automatically create the loop for you if you select the correct inputs:

    select actions

    Instead of initializing the two arrays at the beginning, do it after the loop, and use the output from the selects as the initial values for your arrays.

    enter image description here

    The last step is to remove the duplicates from each array. Create a compose action for each array, and use the following expression:

    remove duplicates