Building A Looping Flow - Query Power BI Within The Loop

[BrewerHaHa]

I have posted my question before but unfortunately the dataset is a lot larger than expected and it has caused me to try a new method. Currently I have a dataset which is like the below in Power BI. I am trying to send out automated emails to the store manager but only each store manager will get information about their own store. They should not get information about other stores.

Date	Store Name	Item Code	Sales	Sales Target	Store Manager Name	Store Manager Email	Staff Employed
01/01/2024	City A	10001	6	7	April	April@zzz.com	5
01/01/2024	City A	10002	9	4	April	April@zzz.com	5
01/01/2024	City A	10003	4	5	April	April@zzz.com	5
01/01/2024	City B	10001	2	2	Mikey	Mikey@zzz.com	3
01/01/2024	City B	10002	0	4	Mikey	Mikey@zzz.com	3
01/01/2024	City C	10002	5	7	Raph	Raph@zzz.com	2
01/01/2024	City C	10003	9	9	Raph	Raph@zzz.com	2

I learnt that there is a 100,000 row limit/1 million cell limit for data queries so I have to pivot and come up with a different solution.

Previously, my flow looked like the below which was excellent but since the data set is so large, a lot of it is missing.

I have attempted to adapt the above flow into the below. My thought process was create a variable for the store name outside of the loop. Then to use use the store name variable to filter on my Power BI query in the loop. Then run a query for each store and send out the emails. So then I would have 100,000 rows per store, rather than 100,000 rows for all stores.

I have included a screenshot of my flow below but I'm unsure what is going wrong. To me I think it is going wrong at the variable stage where it does not get the store name from the first Power BI query and use in the second Power BI query.

For "Step 2 Run a query against a dataset", I can see the output is just the store names.

For "Step 4 Select" I can see the inputs for this are just the store names but the outputs are empty:

{

    "body": [

        {

            "": ""

        },

        {

            "": ""

        },

        {

            "": ""

        },

        {

            "": ""

        },

        {

            "": ""

        }

    ]

}

For "Step 6/7 Run a query against a dataset 1/Create a CSV": I have put variable at the very start of the query which should hopefully run through each lab individually. You can see the code in the screenshot above.

When I look at the output, it seems that all the stores have not been separated, they are in a single output rather than one for each store. So this is where I think the issue is.

For "Step 8 Send an email v2": I get this error below.

One or more recipients are invalid.
clientRequestId: 110ceaa8-5dec-413c-8bbe-daae6dfb6588
serviceRequestId: 2b8c70b7-6d92-0da9-3811-b14b768bf959

[v-jefferni · Accepted solution]

Hi @BrewerHaHa ,

Step 3 and 4 are not needed. In step5, Apply to each input is outputs of step2.

In step6, for the query, you need to replace "@{variables('Store Name')}" with "@items('Apply_to_each')".

In step8, make sure the expression for To is something like

body('Run_a_query_against_a_dataset_1')?['Store Manager Email'][0]

Best regards,

[BrewerHaHa]

Hi @v-jefferni  Thanks for your assistance!

I have tried some of your suggestions and unfortunately I could not manage to make it work. I have a screenshot of my flow below so it is easier to talk through. 

The error happens in Step 4 of the flow. The error I get is 'Action 'Run_a_query_against_a_dataset_1' failed'

I think that is because the query that it is trying to run is:

VAR __DS0FilterTable =
TREATAS({"{"Table[Store Name]":"City A"}"}, 'Table'[Store Name])

BUT when I look at the code in Power BI, it is meant to look like 

VAR __DS0FilterTable =
TREATAS({"City A"}, 'Table'[Store Name])

I think that this part of the query is not correct but I'm not sure how to just get the value, not the table name/column name also.

[BrewerHaHa]

In step 3, I tried to isolate the Table[Store Name]": section by writing the formula 

Mid( items('Apply_to_each'), 18)

I was hoping that this would leave me with just the name of the store e.g. City A but instead I had an error which I copy and pasted below

The template function 'Mid' is not defined or not valid.

[BrewerHaHa]

I have also attempted to add a step between 2 and 3 like the below and added a compose step. I thought I could use a replace function to remove the unnecessary parts and only bring in the store name.

In the compose step, I had the code:

replace(outputs('Run_a_query_against_a_dataset')?['body'],'Table[Store Name]":','')

Unfortunately I had the error

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'replace' expects its first parameter 'string' to be a string. The provided value is of type 'Object'. Please see https://aka.ms/logicexpressions#replace for usage details.'.

[BrewerHaHa]

I think I have managed to make it all work, I am just unsure how to send the email to the correct recipient.

So in this first screenshot, I have run a query, created the variable and used a step to convert the array into a string. This is just a string with the names of the stores. After that I've used a tutorial to remove all special characters from this link: https://www.crmonce.com/how-to-replace-all-special-characters-from-the-string-using-power-automate/

After that I have the output of the name of the store and it should be clean. I then use 2 'Find and replace' to clean the names further. Currently the output is a string which lets me use the 'Find and replace' since this does not work while the data is an array. I then initialize a variable and transform the string back into an array.

After that I run the dax query but use the new clean store names which have gone through the removal of special characters as well as the 2 'Find and replace' steps.

The last thing that I don't understand is the email. At the moment, it keeps failing. Do you have any additional knowledge as to why? I tried this code:

body('Run_a_query_against_a_dataset_1')?['Store Manager Email'][0]

I also tried

body('Run_a_query_against_a_dataset_1')?['Table[Store Manager Email]'][0]

 but neither worked.

This is the error:

Unable to process template language expressions in action 'Send_an_email_(V2)' inputs at line '0' and column '0': 'The template language expression 'body('Run_a_query_against_a_dataset_1')?['Email'][0]' cannot be evaluated because property '0' cannot be selected. Please see https://aka.ms/logicexpressions for usage details.'.

[BrewerHaHa]

This was very helpful but the email required an additional step using 'Compose'. Otherwise, this was a great help!

[v-jefferni · Accepted solution]

Hi @BrewerHaHa ,

The number tag should follow the body of an array to get the first item, then can get a specific column value from this item. So, you can try the expression:

body('Run_a_query_against_a_dataset_1')[0]?['Table[Store Manager Email]']

Best regards,

[BrewerHaHa]

Would you know why this is happening to my query?

So when I extract the store names, I turn an array into a string, do some find and replace, then transform it back into an array.

I always get this \n at the end of the last store name. Would you have any ideas?

This makes it into my query and looks like it's trying to take a new line when I don't want it to.

VAR __DS0FilterTable =
TREATAS({"Astro Store    <------ A NEW LINE HERE?
"}, 'Table'[Store Name])

Normally it would be like:

VAR __DS0FilterTable =
TREATAS({"Astro Store"}, 'Table'[Store Name])

Why is this happening and how do I stop it?

[v-jefferni]

Hi @BrewerHaHa ,

It seems there is a new line tag at the end of the string. How did you generate it? To deal with this kind of issue, we usually use below expression to remove the tag:

replace('BasicString', decodeUriComponent('%0A'), '')

You can also try and use trim function, see if it can remove the blank tail from the string.

Best regards,

[BrewerHaHa]

Yes that works!

I realised that it happens much earlier than my screenshot above. In the screenshot above it looks like the \n is added when in the 'Initialize Variable' step. It is actually in the step of the flow where I remove all the special characters and get the output.

When I click on the input/output, I can see this is the step where the \n is added

Using the code you gave me, I just did a 'Compose step' and did a find and replace here. That seems to have removed the \n!

Thanks Jeffer Ni!