Forum Discussion
Solved
Planner Multiple Assigned Task Filter
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 recogni...
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.
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/
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!
Thank you. Best solution so far. ben_project
