Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cadsystems
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
v-ljerr-msft
Employee
Employee

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

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.  

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors