Analyzing 365 License export

New Contributor

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 nameLicenses
User1@testcompany.co.nzPower BI Pro+Microsoft 365 Business Premium+Azure Active Directory Premium P2+SharePoint (Plan 2)+Power BI (free)
User2@testcompany.co.nzPower BI Pro+Microsoft 365 Business Premium+Azure Active Directory Premium P2+SharePoint (Plan 2)+Power BI (free)
User3@testcompany.co.nzPower BI Pro+Microsoft 365 Business Premium+Azure Active Directory Premium P2+SharePoint (Plan 2)+Power BI (free)
User4@testcompany.co.nzPower BI Pro+Microsoft Power Automate Free+Microsoft 365 Business Premium+Azure Active Directory Premium P2+SharePoint (Plan 2)+Power BI (free)
User5@testcompany.co.nzPower 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 nameLicenses
User1@testcompany.co.nzPower BI Pro
User1@testcompany.co.nzMicrosoft 365 Business Premium
User1@testcompany.co.nzAzure Active Directory Premium P2
User1@testcompany.co.nzSharePoint (Plan 2)
User1@testcompany.co.nzPower BI (free)
User2@testcompany.co.nzPower BI Pro
User2@testcompany.co.nzMicrosoft 365 Business Premium
User2@testcompany.co.nzAzure Active Directory Premium P2
User2@testcompany.co.nzSharePoint (Plan 2)
User2@testcompany.co.nzPower BI (free)
User3@testcompany.co.nzPower BI Pro
User3@testcompany.co.nzMicrosoft 365 Business Premium
User3@testcompany.co.nzAzure Active Directory Premium P2
User3@testcompany.co.nzSharePoint (Plan 2)
User3@testcompany.co.nzPower BI (free)
User4@testcompany.co.nzPower BI Pro
User4@testcompany.co.nzMicrosoft Power Automate Free
User4@testcompany.co.nzMicrosoft 365 Business Premium
User4@testcompany.co.nzAzure Active Directory Premium P2
User4@testcompany.co.nzSharePoint (Plan 2)
User4@testcompany.co.nzPower BI (free)
User5@testcompany.co.nzPower BI Pro
User5@testcompany.co.nzMicrosoft Power Automate Free
User5@testcompany.co.nzMicrosoft 365 Business Premium
User5@testcompany.co.nzAzure Active Directory Premium P2
User5@testcompany.co.nzPower BI (free)

 

3 Replies

@Mike_Stroud 

Power Query could work - split text in second column to list and expand it. Please see attached.

@Sergei Baklan - Thats great, so simple.

 

Thanks

Mike

@Mike_Stroud , glad it helped