web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : zAxHJ1p5a7viyEk4H4qxNg
Power Platform Community / Forums / Power Automate - Building Flows / Convert power BI table...
Power Automate - Building Flows
Answered

Convert power BI table to JSON to array

Like (0) ShareShare
ReportReport
Posted on 4 May 2023 13:23:53 by Community Power Pla...

I'm building this flow where I parse JSON from a query against Power BI to later create some conditional logic based on the values inside the array. 

 

Unluckily I get this error: The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@@body('Parse_JSON')?['@{variables('New Array')}']'
' is of type 'String'. The result must be a valid array.

 

And this is my squema: 

{
  "type": "object",
  "properties": {
    "body": {
      "type": "object",
      "properties": {
        "results": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": {
              "tables": {
                "type": "array",
                "items": {
                  "type": "object",
                  "properties": {
                    "rows": {
                      "type": "array",
                      "items": {
                        "type": "object",
                        "properties": {
                          "ShortView Mix[Source]": {
                            "type": "string"
                          },
                          "ShortView Mix[Status]": {
                            "type": "string"
                          },
                          "[IsGrandTotalRowTotal]": {
                            "type": "boolean"
                          },
                          "[SumCount]": {
                            "type": "integer"
                          }
                        },
                        "required": [
                          "[IsGrandTotalRowTotal]",
                          "[SumCount]"
                        ]
                      }
                    }
                  },
                  "required": [
                    "rows"
                  ]
                }
              }
            },
            "required": [
              "tables"
            ]
          }
        },
        "firstTableRows": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": {
              "ShortView Mix[Source]": {
                "type": "string"
              },
              "ShortView Mix[Status]": {
                "type": "string"
              },
              "[IsGrandTotalRowTotal]": {
                "type": "boolean"
              },
              "[SumCount]": {
                "type": "integer"
              }
            },
            "required": [
              "[IsGrandTotalRowTotal]",
              "[SumCount]"
            ]
          }
        }
      }
    }
  }
}

 

nibgli_0-1683206363202.png

nibgli_1-1683206603107.png

 

 

I have the same question (1)
  • Verified answer
    Pstork1 Profile Picture
    Pstork1 67,779 Most Valuable Professional on 04 May 2023 at 13:28:08
    Re: Convert power BI table to JSON to array

    I suspect you are parsing the JSON from the object returned by Power BI and not the "results" property.  Results is the array.  If your current schema is working for you then just make sure you use the Results dynamic content in your loop.  Or when you get the content back from Power BI make sure you use the Results property and not the whole body.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

MS.Ragavendar – Community Spotlight

We are honored to recognize Ragavendar Swaminatha Subramanian as our September…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 783 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 515 Moderator

#3
trice602 Profile Picture

trice602 299 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Share your ideas and struggles with building Power Automate flows!
Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans