Load REST API in Power BI.

    Our plan today

    1. Find REST API we want to use
    2. Connect to REST API in Power BI
    3. Create tables and structure the data

    1. Find REST API we want to use

    For this we are going to use the Azure Lab Services REST API.

    Link to the documentation is here https://docs.microsoft.com/en-us/rest/api/labservices/

    2. Connect to REST API in Power BI

    1. Create a new Power BI Report
    2. Click on Transform data
    3. Click New Source
    4. Select Blank Query
    5. Click Advanced Editor and paste in the following:
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    let GetManagementBaseUri=(govKind as text) as text =>
    let
        managementUrl = if govKind ="us-government" then "https://management.usgovcloudapi.net"
                    else if govKind="germany-government" then""
                    else if govKind = "china" then "https://management.chinacloudapi.cn"
                    else "https://management.azure.com"

    in
        managementUrl

in GetManagementBaseUri
    1. Rename the Blank Query to GetManagementURL
    2. Create a new blank query
    3. Paste in the following:
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

    let
 GetPages = (Path)=>
 let
     Source = Json.Document(Web.Contents(Path)),
     LL= @Source[value],
     result = try @LL & @GetPages(Source[#"nextLink"]) otherwise @LL
 in
 result,
     Fullset = GetPages(GetManagementURL(AzureKind)&"/subscriptions?api-version=2020-01-01"),
     #"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "authorizationSource", "managedByTenants", "subscriptionId", "tenantId", "displayName", "state", "subscriptionPolicies", "tags"}, {"id", "authorizationSource", "managedByTenants", "subscriptionId", "tenantId", "displayName", "state", "subscriptionPolicies", "tags"}),
     #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"managedByTenants"}),
     #"Expanded subscriptionPolicies" = Table.ExpandRecordColumn(#"Removed Columns", "subscriptionPolicies", {"locationPlacementId", "quotaId", "spendingLimit"}, {"locationPlacementId", "quotaId", "spendingLimit"}),
     #"Removed Columns1" = Table.RemoveColumns(#"Expanded subscriptionPolicies",{"tags"}),
     #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"displayName", "Subscription Name"}}),
     #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"id"})
 in
     #"Removed Columns2"
    1. Rename to All Subscriptions
    2. Create a new blank query
    3. paste in the following:
    1

    "global" meta [IsParameterQuery=true, List={"us-government", "global", "china"}, DefaultValue="global", Type="Text", IsParameterQueryRequired=true]
    1. Rename to AzureKind

    e1feef23-e9e3-43a7-9d4c-4298173cd19e rg-prd-lab-dsk-55-502819 prd-lab-dsk-55-502819 55-502819-Network Services and Administration