Planner to Excel - Bucket name

[Fess67]

Hi,

I am exporting Planner information to excel to build better reports.  I can export to Excel ok but I cannot get rid of the meaningless Bucket Id gibberish and replace it with the actual bucket name.

So what I am trying to do is update the row to replace the Bucket id with the Bucket name.  Flow fails as no row was found with id 'xxxxxx'

Is there a way to replace the Bucket id with the Bucket name.  Many thanks for taking the time to look.

[Fess67]

Some progress made.

I can now retrieve the bucket name using List Buckets and then I am trying to update the Bucket Name column but it is looping though and returning the 'To Do' bucket which is the default first bucket.  I watch it loop through the buckets but cannot seem to get it to select the correct one.

I can also get it to display the correct bucket name but it does each task 5 times - 1 for each bucket, instead of just once for the correct bucket.

[rzaneti]

Hi @Fess67 ,

You are almost there 🙂

I created the following table to replicate yours (multiple records related to 2 ids):

You will need not only your 'List buckets' action, but also a 'List rows present in a table', and identify the table that you want to update:

After that, you will need only one loop, and it will map the 'value' property from your 'List rows present in a table' action. Inside this loop, you will need a filter array, but at this time, filter the 'List buckets' value. For the filter input, insert the 'value id' property from 'List buckets' in the left side (highlighted in blue), set 'is equal to' in the dropdown (highlighted in green) and insert the 'Bucket' dynamic content from your 'List rows present in a table' in the right side (highlighted in yellow): 

Now we will update the Excel file. Enter the details about your Excel table and indicate the 'Key column' as your task id (or as any other column with unique values only). As key value, select the dynamic content of your 'Task Id' from your 'List rows present in a table' action (highlighted in yellow). IN the bucket input, enter the following expression: body('Filter_array')[0]['name']. To insert an expression, open the dynamic content pop up, change it to the 'Expression' tab (highlighted in blue) and enter the expression content into the 'Fx' input (highlighted in pink):

To understand a little more about how to update Excel tables with Power Automate, I recommend you this article, which is part of a series of posts about the integration of both tools: http://digitalmill.net/2023/07/24/inserting-and-updating-data-into-excel-tables-with-power-automate/ 

After run the flow, this is the output:

If you have any trouble with dynamic contents or expressions, check this article: http://digitalmill.net/2023/07/31/using-dynamic-content-in-power-automate/ 

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/

[Fess67]

@rzaneti thanks for taking the time.  As you say, almost there' 🙂

I am confused about filter array:

1. It is called (F) Filter array - yet we refer to it as (f) filter_array.  I am guessing that is some syntax convention with Power Automate?
2. The From shows me all the bucket names and id's as expected.
3. The output is just [ ]
   1. So when I am referencing it as body('filter_array')[0]['name'[
   2. what does the [0] refer to?
   3. I understand 'name' and that is exactly what I want returned but am getting the following error

[rzaneti]

Hi @Fess67 ,

For any reason, your 'Filter array' is returning an empty string. It usually happens when you do not have any value inside the array that matches to the filter condition.

Could you please share an image from your 'Filter array' action in edit mode? It will show what exactly are you using as filter condition.

[Fess67]

Thanks @rzaneti 

[rzaneti · Accepted solution]

Hi @Fess67 ,

Some changes here:

1. In your 'Apply to each', remove the dynamic content that you have and include the 'value' property from your Excel action.

2. In your 'Filter array', remove the dynamic content at the right side of the 'is equal to' and include the 'Bucket' property from your Excel action. 

[Fess67]

Woohoo we have a working product  🙂

This is what I ended up with, I have tested and it works.  Thank you so much for your guidance.

[Grace2019]

Hi @Fess67 

Do you mind sharing your flow with details with pic here? I still have difficulties to download planner to excel using flow. what I want to achieve is to update the excel everyday with the whole data of planner.

Thanks,

Grace