08-01-2019 07:06 AM
08-01-2019 07:06 AM
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?
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.
08-02-2019 12:44 AMSolution
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...
08-02-2019 03:16 AM
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.
08-19-2019 06:49 AM
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!