SOLVED

Planner Multiple Assigned Task Filter

Copper Contributor

Hello Community,

 

I have a question related to Microsoft Planner Export to Excel function. It gives multiple assigned task values with semi colon as "Name1 Surname1; Name2 Surname2". It is recognized as one collapsed data. I want to be able to filter as two different person with this data so I can get correct "Members Assignment Chart". What is the best possible way for this?

 

Additional Information

 

These "Name1 Surname1; Name2 Surname2" persons are belong to a office365 group. So I tried to to export this data to an online Sharepoint list. The names are recognized as People inside the group. Then I tried to connect this to Power BI. But the values are recognized as a List. When I extracted the values, again it was recognized as one data in row. 

6 Replies
best response confirmed by kaanbodur (Copper Contributor)
Solution

Hi @kaanbodur ,

There are a couple of ways to tackle this. 

i) you could use a formula to extract the names to a new columns.   To get the left most name use the formula =LEFT(E6,(FIND(";",E6,1)-1)), and the right name =RIGHT(E6,LEN(E6)-FIND(";",E6)) .  If you had 3 names then you would need to handle this differently. Ultimately I think that this soln is limited because you'd have to develop a formula to handle 3,4,5 names etc.

ii) Save the Excel file, and then use either Get Data from within Excel, or Get Data from within Power BI, and use the Split Column button in Power Query (in either Excel or Power BI) and split the column by the ";" delimiter.   IMHO this is the better solution as it will handle use case of different numbers of assignees.  This attached image should help a little bit.  You can also clean up the file by removing the top few rows, promoting headers etc...

Hi @ben_project,

 

Thank you for these solution offers. Second option worked for me better. I expanded the data as rows with Split Column button.

 

For planner reporting, going with the row option is very useful since it is possible to count distinct values of rows. Planner Export option gives unique task id for this purpose I believe. 

 

clipboard_image_0.png

 

Maybe I can give more details about my reporting solution. I uploaded my exported excel to a SharePoint document library. Then I've used the web option for getting data into the Power BI. I followed this suggestion.

 

Thanks again for your help. 

@kaanbodur- that makes sense. The issue is enterprise reporting, ie how report across multiple plans and provide a level of automation.  I feel a blog post coming on that addresses the 1st issue at least...

Power BI template for Planner tasks @ https://applepark.co.uk/powerbi-planner-template/ 

Thank you. Best solution so far. @ben_project 

I actually like the idea of writing a flow app that that exports the data to a SharePoint list and using Power BI to read that that list.   I might write a blog post about this sometime soon!

1 best response

Accepted Solutions
best response confirmed by kaanbodur (Copper Contributor)
Solution

Hi @kaanbodur ,

There are a couple of ways to tackle this. 

i) you could use a formula to extract the names to a new columns.   To get the left most name use the formula =LEFT(E6,(FIND(";",E6,1)-1)), and the right name =RIGHT(E6,LEN(E6)-FIND(";",E6)) .  If you had 3 names then you would need to handle this differently. Ultimately I think that this soln is limited because you'd have to develop a formula to handle 3,4,5 names etc.

ii) Save the Excel file, and then use either Get Data from within Excel, or Get Data from within Power BI, and use the Split Column button in Power Query (in either Excel or Power BI) and split the column by the ";" delimiter.   IMHO this is the better solution as it will handle use case of different numbers of assignees.  This attached image should help a little bit.  You can also clean up the file by removing the top few rows, promoting headers etc...

View solution in original post