extract data from output

[Vstar19]

Hello. I am running a query against a Power BI Dataset, and the output is returned as below

[

  {

    "[IsGrandTotalRowTotal]": true,

    "[DistinctCountName]": 29,

    "[CountDashboardId]": 1,

    "[Datamarts___All]": "0",

    "[Usage___All]": 38,

    "[Monthly_Active_Users__]": 1

  },

  {

    "Workspaces[Name]": "Sample name",

    "Workspaces[Workspaces.PrimaryOwner.EMail]": "name@company.com",

    "[IsGrandTotalRowTotal]": false,

    "[DistinctCountName]": 1,

    "[Datamarts___All]": "0",

    "[Usage___All]": 0

  },

  {

    "Workspaces[Name]": "workspace 2",

    "Workspaces[Workspaces.PrimaryOwner.EMail]": "name@company.com",

    "[IsGrandTotalRowTotal]": false,

    "[DistinctCountName]": 1,

    "[Datamarts___All]": "0",

    "[Usage___All]": 0

  },

  {

    "Workspaces[Name]": "Workspace 3",

    "Workspaces[Workspaces.PrimaryOwner.EMail]": "name@company.com",

    "[IsGrandTotalRowTotal]": false,

    "[DistinctCountName]": 1,

    "[Datamarts___All]": "0",

    "[Usage___All]": 0

  }

]

I am trying to write an apply to each, so i can get each unique Workspaces[Workspaces.PrimaryOwner.EMail address, so that i can email them . 

How do i do this.. Ive tried to parse it, but i get the below error : The schema validation failed.

I dont understand. 

I just want an apply to each who list all email address for now. 

What do i do?

[lbendlin]

Go back to Power BI, simplify the visual to a table visual without totals, then copy the DAX code again. This time the query will be much simpler, and you can ingest it as is for your email.

[jitheshk]

Instead of parsing try expression:
Inside your Apply to each (which as the output form the Power BI dataset)

Agains the variable in which you want to provide the value of email id use the below expression: 

item().?['Workspaces[Workspaces.PrimaryOwner.EMail]']

[SanmeshG]

Hi @Vstar19 ,

Here the first JSON object seems to represent the overall numbers , so I think this can be skipped. So , you can generate the JSON schema for your Parse JSON action by skipping the first JSON object.

Considering using the below action for that (or generate the schema by taking the remaining objects manually except first one).

skip(outputs('Compose'),1)

Here in place outputs('Compose') use the name of the action that you are using in your flow.

If this helps , please give this a thumbs up and mark this as a solution.

Thanks,

Sanmesh

[Vstar19]

I did this, but I get this error : 

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@item()?['Workspaces[Workspaces.PrimaryOwner.EMail]'] is of type 'Null'. The result must be a valid array.