Sharing my Power Automate flow: Get query results using the new Query connectors!

@Advancement Division
A 2nd post to talk about CSV output format.

There are a few reason to use CSV as output format rather than JSON:

1. csv is itself already a file type that can open in excel, so sometime you may just want to send a csv (as an excel report) as attachment in email.
   1. Or you may want to save the CSV directly to SharePoint or cloud location, this is useful if you have Power BI build that refresh from a CSV file on SharePoint
2. TOO MUCH data. Power Automate has a data size limit, so depending on how many rows of data and column of data, you may exceed that limit. https://learn.microsoft.com/en-us/power-automate/limits-and-config#request-limits
   1. Since a JSON output format will take up more BYTE than CSV for the same amount of rows/column of data

so if you did use CSV output format, you can turn the CSV data into JSON easily with the Select action.

skip(split(trim(replace(replace(base64ToString(body('HTTP_Get_Query_Result_CSV')?['$content']), '",','|'), '"', '')), outputs('Compose_New_Line')), 1)

so the expression starts (meaning the inner most expression) with the base64ToString (same as JSON output), then there are 2 replace(): (1) replace “, with a separator character that is unlikely to be real data (i.e. pipeline character |); (2) replace ” with empty string.

So for example, the CSV data of:
“123”,"John Smith","Male","123 Main St."

to (first replace)
“123|”John Smith|"Male|"123 Main St."

to (2nd replace)
123|John Smith|Male|123 Main St.

Then the trim() will remove any leading or trailing whitespace

Then the split() will split the CSV by a new line character. (which I had a compose action that have 1 character in it, “enter” key, which is a newline character), this creates an array of string (i.e. 123|John Smith|Male|123 Main St.) that will be used in the Select action.

finally skip() will skip the first line in the data (which is the header row of the CSV.

the “map” field of the Select action will create the JSON properties you want from the CSV.

so the first column is the name of the property in JSON that can be referenced as dynamic content as subsequent actions.

the 2nd column is the value, which are:
split(item(), '|')?[0]
split(item(), '|')?[1]
split(item(), '|')?[2]
etc

so basically will use the split() expression on each string in the array (i.e. 123|John Smith|Male|123 Main St.), split by the pipeline character, and then accessing each element by an index.

123|John Smith|Male|123 Main St. becomes an array of
["123","John Smith","Male","123 Main St."]

[0] is the first element which is “123”
[1] is the 2nd element which is “John Smith”
etc

Good luck.

WARNING: the above expression will have issue when you have data that have “new line” character in the column. (i.e. Address lines, which has more than 1 line of address)

@Shaun Germany
not sure what you meanb y mixed date format. please use example.

you should check from the “source” to see where the issue is. Meaning, start with your Query API right after getting the CSV file (or JSON), what does the output look like.

@Shaun Germany
not sure what you mean by “looks ok”. So in the download data after execute query, the 2 date format exists or they are uniformly 1 date format? if 2 format, which field is UK vs US?

Power BI does have a “regional setting” so you will want to confirm that, otherwise, you will do some Power Query to transform the data which shouldn't be too difficult.

@Carol Grant
Don't think it's the same issue as yours.

It sounds like you are copying from PBI into excel. you need to update the format of the date in Power BI such that it is in the format you want. By default, it will always be Weekday, Month day, year.

Instead change it to

@Ashley Moose Thank you, Ashley! Successfully imported this template using RE Query API. Now, I am trying to figure out how to simply have the results of the query emailed to me rather than creating the csv file? So far, I have only gotten the gift ids emailed to me? Thanks for any suggestions on how to switch out the “save csv file” to emailing the query results.

@Ashley Moose Thanks for responding. Eventually want to learn both ways but for now, entire results set emailed as a table in the body of email. I thought I heard in one of the videos I've watched that this can be done without having to get each part of the body as we did using Get List.

@Ashley Moose I created an ad hoc query using the RENXT Query Connector, and I am trying to utilize the data without creating a csv, as all the responses are on one line for one constituent. This is the output I get when I view the output of the body of the response:

{

"$content-encoding": "cp1252",

"$content-type": "text/csv",

"$content": "Y2Z5QW1vdW50LGNmeUNvdW50LHBmeUFtb3VudCxwZnlDb3VudCwycGZ5QW1vdW50LDJwZnlDb3VudCxyZWN1ckFtb3VudCxyZWN1ckNvdW50LFFSRUNJRA0KIiQ3NSw1MDAuMDAiLCIyIiwiJDc1LDY1MC4wMCIsIjMiLCIkNzUsNTAwLjAwIiwiMiIsIiQ1MDAuMDAiLCIxIiwiNDU0MiI="

}

And when I display the body results on screen, I see this:

cfyAmount,cfyCount,pfyAmount,pfyCount,2pfyAmount,2pfyCount,recurAmount,recurCount,QRECID "$75,500.00","2","$75,650.00","3","$75,500.00","2","$500.00","1","4542"

How can I get this into a working format to call on these data points? I feel like I am missing something obvious!!

@Alex Wong Thanks! I ended up using the Send HTTP Request and making the output JSON. I will try this other method out as well.

@Ashley Moose
Thanks for the template. I would like to import it, but as my school is a Google shop, I don't have One Drive for Business or Office365 Outlook. So, I can't connect them while importing the flow. Do you have any suggestions? I am not good enough to open JSON and edit the flow with GMail and Google Drive connections before importing your template.

@Alex Wong I've just started looking at this again. And your solution was what I assumed I'd need to do, but I've so far not got it to work. The column containing the dates has some cells are formatted to a “Custom Date” and some to “General” (when viewed in Excel immediately after PA exports it). Attempting to set the formatting in Power BI either results (upon trying to refresh the data) in some of the cells becoming “error” or a mix of date formats e.g. 1/12/2024 or 12/1/2024.