cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

query

I'm trying to create a dial that uses the query of licensed users as the pointer and Valid Licenses as the endpoint.  I have no problem with the first part but the Valid License isn't a built in query for the Office 365 Adoption template.  I figure that I have to create the query but I don't even know where to start.  Any help would be appreciated.  Thanks

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: query

Hi @cadsystems,

 

If I understand you correctly, you should be able to use the power query below to update the TenantOfficeLicenses query in Query Editor > Advanced Editor to involve the TotalEnabled column for the Valid Licenses in your scenario. Smiley Happy

let
    Source = OData.Feed("https://reports.office.com/pbi/v1.0/" & TenantID,null,[ODataVersion=4]),
    TenantOfficeActivation_table = Source{[Name="TenantOfficeActivation",Signature="table"]}[Data],
    #"Expanded ServicePlans" = Table.ExpandListColumn(TenantOfficeActivation_table, "ServicePlans"),
    #"Expanded ServicePlans1" = Table.ExpandRecordColumn(#"Expanded ServicePlans", "ServicePlans", {"ServicePlanName", "TotalEnabled", "TotalActivated", "TotalCount", "AndroidCount", "iOSCount", "MacCount", "PcCount", "WinRtCount"}, {"ServicePlanName", "TotalEnabled", "TotalActivated", "TotalCount", "AndroidCount", "iOSCount", "MacCount", "PcCount", "WinRtCount"}),
    #"Expanded Licenses" = Table.ExpandListColumn(#"Expanded ServicePlans1", "Licenses"),
    #"Expanded Licenses1" = Table.ExpandRecordColumn(#"Expanded Licenses", "Licenses", {"LicenseName", "AssignedCount"}, {"LicenseName", "AssignedCount"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Licenses1",{"ServicePlanName", "TotalEnabled", "LicenseName", "AssignedCount", "ContentDate", "TimeFrame"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"TimeFrame", type date}})
in
    #"Changed Type"

liscense1.PNG

 

Regards

Highlighted
Regular Visitor

Re: query

That didn't give me what I was expecting.  I think we are close though.  In my scenario let's say I have purchased 75 E3 licenses.  Of those licenses let's say I have assigned 68 of those licenses to users.  What I want is the pointer to start at the assigned Licenses of 68 which I can already do.  It's built into a query already.  Then what I want to do is set the actual end to be the total number of E3 license that I have purchased "75".  I'm trying to build a query that gives me the total number of licenses that the portal shows that I own.  In the portal they refer to them as "Valid Licenses".  If I can do that then I can create a report that gives managers the ability to see when they are getting close to the number of licenses I'm hoping to create workflows for either removing stale licenses for termed users or purchase more licenses for onboarding new users.  

 

 

Highlighted
Microsoft
Microsoft

Re: query

Hi @cadsystems,

 

Thanks for the detailed explanation! However, after a few more try, I still cannot get the "Valid Licenses" information from the office portal. Smiley Mad

 

Regards

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors