Skip to main content

Notifications

Power Automate - Building Flows
Unanswered

PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

(0) ShareShare
ReportReport
Posted on by 101

I have created a flow which when Power apps has button pressed , I wanted to bring in some data from a powerbi table and save that data as a collection using this script

Set(crm_skeds,crm_skeds_aged.Run().skeds_aged);
ForAll(Table(ParseJSON(crm_skeds)),Collect(collData,{Code: ThisRecord.Value.Code}))

 

My flow is reading the data from pbi fine , but it is adding [] to the column names , which I think is one of the issues

Code to read in data is

EVALUATE
SELECTCOLUMNS(
TOPN(1000, crm),
"carrier_code", crm[carrier_code],
"region", crm[region],
"certified_date", crm[certified_date],
"received_date", crm[received_date]
)

 

After reading in the code , I added an initialize variable

{
  "type": "InitializeVariable",
  "inputs": {
    "variables": [
      {
        "name": "vrSkeds",
        "type": "array",
        "value": "@outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']"
      }
    ]
  },
  "runAfter": {
    "Run_a_query_against_a_dataset": [
      "Succeeded"
    ]
  }
}
 
and the respond to a power app or flow is
{
  "type": "Response",
  "kind": "Http",
  "inputs": {
    "statusCode": 200,
    "body": {
      "skeds_aged": "@{variables('vrSkeds')}"
    }
  },
  "runAfter": {
    "Initialize_variable": [
      "Succeeded"
    ]
  }
}
  • JD_26 Profile Picture
    JD_26 101 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

    If I use first table rows , it is like

    [

    "XK,EUR,2024-05-29T00:00:00\r",
    ""
    ]

    i can find a way to remove the \r 

    I tried this but it never worked , if(empty(replace(split(item(), ',')[1], '\r', '')), null, replace(split(item(), ',')[1], '\r', '')) , I am trying to bring in data from powerbi and add or append data in a sharepoint list , based on code , if code exist append , if not add. 

  • lbendlin Profile Picture
    lbendlin 7,597 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

    yes, you can run a select or compose.

     

    But I am surprised that you even need to do that. Usually the FirstTableRows comes over as a ready-to use array.

  • JD_26 Profile Picture
    JD_26 101 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

    Thanks , is there a step I can add in power automate to fix this please

  • lbendlin Profile Picture
    lbendlin 7,597 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

    Your output is nearly usable. The issue is not the square brackets in the field names, but the extra double quotes around the array.

     

    {
     "skeds_aged": [
     {
     "[carrier_code]": "DD",
     "[region]": "SEA",
     "[certified_date]": "2024-05-24T00:00:00",
     "[received_date]": "2024-05-24T00:00:00"
     },
     {
     "[carrier_code]": "8B",
     "[region]": "SEA",
     "[certified_date]": "2024-05-24T00:00:00",
     "[received_date]": "2024-05-24T00:00:00"
     }
     ]
    }
  • JD_26 Profile Picture
    JD_26 101 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data
    The output is like this
    {
    "skeds_aged": "[{\"[carrier_code]\":\"DD\",\"[region]\":\"SEA\",\"[certified_date]\":\"2024-05-24T00:00:00\",\"[received_date]\":\"2024-05-24T00:00:00\"},{\"[carrier_code]\":\"8B\",\"[region]\":\"SEA\",\"[certified_date]\":\"2024-05-24T00:00:00\",\"[received_date]\":\"2024-05-24T00:00:00\"}]"
    }
    When I run this script in power apps I do get the same output , If use crm_skeds , but I need to parse the json so I can get it saved in a collection , I think I need to remove those brackets like [carrier_code]
     

     

     

     

     

  • lbendlin Profile Picture
    lbendlin 7,597 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

    show a sample for

     

    @outputs('Run_a_query_against_a_dataset')?['body/firstTableRows']

  • JD_26 Profile Picture
    JD_26 101 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

    can you show me how to do this in the code as I am not understanding what is required , thankyou

  • lbendlin Profile Picture
    lbendlin 7,597 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

    treat the square brackets as part of the field name

     

    so instead of (for example) 

     

    variables('Column1')

     

    use 

     

    variables('[Column1]')

  • JD_26 Profile Picture
    JD_26 101 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data

    I am getting stuck on the bringing data back to power apps , in the last step , as I believe it is no longer in a correct json format so is not being read , i did see some info saying add a replace [] for the column names to get it in a correct readable json format

  • lbendlin Profile Picture
    lbendlin 7,597 on at
    Re: PBI run a query against a dataset issue when using respond to Power App flow , output not in a correct json format so power apps not getting any data
    My flow is reading the data from pbi fine , but it is adding [] to the column names , which I think is one of the issues

    How is that an issue?  That's how it is designed.

     

    Where are you getting stuck?

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

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,445

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,741

Leaderboard