Answered

PowerApps MS Flow and Power Query

(0) Share

Report

Posted on by BrendonA

I'm having issues bringing the result of Power Query and Response from Flow into PowerApps.

In general I followed the same steps I've seen other's use with a PowerApps Trigger, SQL Query or Stored Procedure, and a Request Response. But where others have used the SQL Query or Stored Procedure, I am using Power Query.

I am seeing data in the Power Query and in the Response, but I am having issues with getting the results into PowerApps.

From what I can tell my Response schema is properly configured as per the data. But in PowerApps when I run the Flow, my collection doesn't populate with data, it did populate the column headers though.

Here is a sample of the Response Output:

{
"resultType": "JsonValue",
"value": [
{
"Index": 6811,
"Product_Location_Id": 1735,
"Item No_": "580-219",
"Unit of Measure Code": "EA",
"Item Category Code": "19-19",
"Product Group Code": "SQUARE",
"QTY": 2,
"Product_Description": "O-RING, SQUARE",
"Product_ProductLine_Id": "145"
},
{
"Index": 6814,
"Product_Location_Id": 1735,
"Item No_": "580-224",
"Unit of Measure Code": "EA",
"Item Category Code": "19-19",
"Product Group Code": "SQUARE",
"QTY": 5,
"Product_Description": "O-RING, SQUARE",
"Product_ProductLine_Id": "145"
}
]
}

And the Schema:

{
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "Index": {
 "type": "integer"
 },
 "Product_Location_Id": {
 "type": "integer"
 },
 "Item No_": {
 "type": "string"
 },
 "Unit of Measure Code": {
 "type": "string"
 },
 "Item Category Code": {
 "type": "string"
 },
 "Product Group Code": {
 "type": "string"
 },
 "QTY": {
 "type": "integer"
 },
 "Product_Description": {
 "type": "string"
 },
 "Product_ProductLine_Id": {
 "type": "string"
 }
 }
 }
}

And my PowerApps Run Flow code:

ClearCollect(PowerQueryResults,'PowerApp->TransformdatausingPowerQuery'.Run())

Any help is much appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

yashag2255 24,769 Super User 2024 Season 1 on at

Like (1)

Report

Hi @ElevenTech

Based on the response shared by you, I think the issue is with the Schema that you have passed, can you try to update this to below mentioned schema. Also, when this is done, please remove the flow from the powerapps button and add it again.

{
    "type": "object",
    "properties": {
        "resultType": {
            "type": "string"
        },
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "Index": {
                        "type": "integer"
                    },
                    "Product_Location_Id": {
                        "type": "integer"
                    },
                    "Item No_": {
                        "type": "string"
                    },
                    "Unit of Measure Code": {
                        "type": "string"
                    },
                    "Item Category Code": {
                        "type": "string"
                    },
                    "Product Group Code": {
                        "type": "string"
                    },
                    "QTY": {
                        "type": "integer"
                    },
                    "Product_Description": {
                        "type": "string"
                    },
                    "Product_ProductLine_Id": {
                        "type": "string"
                    }
                },
                "required": [
                    "Index",
                    "Product_Location_Id",
                    "Item No_",
                    "Unit of Measure Code",
                    "Item Category Code",
                    "Product Group Code",
                    "QTY",
                    "Product_Description",
                    "Product_ProductLine_Id"
                ]
            }
        }
    }
}

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
BrendonA 15 on at

Like (0)

Report
Thank you @yashag2255 !
By changing that schema I was able to get the results into PowerApps.

The collection ended up bringing stuctured like this:
resultType | value
JsonValue | [table of records]

Once I had that I could access the records with a gallery using.
First(PowerQueryResults).value
Where 'PowerQueryResults' is my Collection.
And the First value record is the table.

Again thank you for the solution!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @yashag2255 ,

i have a same issue, but i tried your solution and it is not working, please help if you have other solution.

here is my flow (in attachment)

and here is my result response :

{
  "Table1": [
    {
      "Area": "Surabaya",
      "DADays": 4,
      "CustomerName": "abc, Sby, PT",
      "SalesmanName": "xx.yy@xxx.com",
      "ProductName": "EONHAND 270",
      "QTY": 12,
      "UOM": "Jerrycan @ 5 Ltr",
      "OpportunityNo": "1234568990",
      "ProductFamily": "Maintenance Chemicals"
    },
    {
      "Area": "Surabaya",
      "DADays": 4,
      "CustomerName": "Tcvb, Sby, PT",
      "SalesmanName": "xx.yy@xxx.com",
      "ProductName": "EONHAND 275",
      "QTY": 4,
      "UOM": "Jerrycan @ 5 Ltr",
      "OpportunityNo": "1234568990",
      "ProductFamily": "Maintenance Chemicals"
    }
  ]
}

and here is my scheme :

{
    "type": "object",
    "properties": {
        "Area": {
            "type": "string"
        },
        "DADays": {
            "type": "integer"
        },
        "CustomerName": {
            "type": "string"
        },
        "SalesmanName": {
            "type": "string"
        },
        "ProductName": {
            "type": "string"
        },
        "QTY": {
            "type": "integer"
        },
        "UOM": {
            "type": "string"
        },
        "OpportunityNo": {
            "type": "string"
        },
        "ProductFamily": {
            "type": "string"
        }
    },
    "required": [
        "Area",
        "DADays",
        "CustomerName",
        "SalesmanName",
        "ProductName",
        "QTY",
        "UOM",
        "OpportunityNo",
        "ProductFamily"
    ]
}

thanks a lot for your help

Screenshot_3.png

Screenshot_1.png

Screenshot_2.png

Was this reply helpful? Yes No