Forum Discussion
Analyzing 365 License export
I have a list of Microsoft 365 Licenses by user but the data is not in very friendly format (See attached file). I can text to column with the + as the eliminator but even then the licenses are not in the same columns so reporting on it is imposable.
How do I get my data from this
| User principal name | Licenses |
| mailto:User1@testcompany.co.nz | Power BI Pro+Microsoft 365 Business Premium+Azure Active Directory Premium P2+SharePoint (Plan 2)+Power BI (free) |
| mailto:User2@testcompany.co.nz | Power BI Pro+Microsoft 365 Business Premium+Azure Active Directory Premium P2+SharePoint (Plan 2)+Power BI (free) |
| mailto:User3@testcompany.co.nz | Power BI Pro+Microsoft 365 Business Premium+Azure Active Directory Premium P2+SharePoint (Plan 2)+Power BI (free) |
| mailto:User4@testcompany.co.nz | Power BI Pro+Microsoft Power Automate Free+Microsoft 365 Business Premium+Azure Active Directory Premium P2+SharePoint (Plan 2)+Power BI (free) |
| mailto:User5@testcompany.co.nz | Power BI Pro+Microsoft Power Automate Free+Microsoft 365 Business Premium+Azure Active Directory Premium P2+Power BI (free) |
To something like this so I can then pivot it for reporting
| User principal name | Licenses |
| mailto:User1@testcompany.co.nz | Power BI Pro |
| mailto:User1@testcompany.co.nz | Microsoft 365 Business Premium |
| mailto:User1@testcompany.co.nz | Azure Active Directory Premium P2 |
| mailto:User1@testcompany.co.nz | SharePoint (Plan 2) |
| mailto:User1@testcompany.co.nz | Power BI (free) |
| mailto:User2@testcompany.co.nz | Power BI Pro |
| mailto:User2@testcompany.co.nz | Microsoft 365 Business Premium |
| mailto:User2@testcompany.co.nz | Azure Active Directory Premium P2 |
| mailto:User2@testcompany.co.nz | SharePoint (Plan 2) |
| mailto:User2@testcompany.co.nz | Power BI (free) |
| mailto:User3@testcompany.co.nz | Power BI Pro |
| mailto:User3@testcompany.co.nz | Microsoft 365 Business Premium |
| mailto:User3@testcompany.co.nz | Azure Active Directory Premium P2 |
| mailto:User3@testcompany.co.nz | SharePoint (Plan 2) |
| mailto:User3@testcompany.co.nz | Power BI (free) |
| mailto:User4@testcompany.co.nz | Power BI Pro |
| mailto:User4@testcompany.co.nz | Microsoft Power Automate Free |
| mailto:User4@testcompany.co.nz | Microsoft 365 Business Premium |
| mailto:User4@testcompany.co.nz | Azure Active Directory Premium P2 |
| mailto:User4@testcompany.co.nz | SharePoint (Plan 2) |
| mailto:User4@testcompany.co.nz | Power BI (free) |
| mailto:User5@testcompany.co.nz | Power BI Pro |
| mailto:User5@testcompany.co.nz | Microsoft Power Automate Free |
| mailto:User5@testcompany.co.nz | Microsoft 365 Business Premium |
| mailto:User5@testcompany.co.nz | Azure Active Directory Premium P2 |
| mailto:User5@testcompany.co.nz | Power BI (free) |
3 Replies
Power Query could work - split text in second column to list and expand it. Please see attached.
Mike_Stroud , glad it helped
