Monday, July 5, 2021

Power Query - Connect API REST

1. Example (in parts)


GetAccessToken:
 () => 
let
    url = "https://wwwXPTO.com/Auth/GetApiToken",
    body  = "{ ""user"": ""userXPTO"", ""pwd"": ""123$""} ",
    AccessToken = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"], Content=Text.ToBinary(body)])),
    access_token = AccessToken[token]
in
    access_token
========================================================
GetList:
let
  // Get_Data
  Query = () => let
    Source = Json.Document(Web.Contents("https://wwwXPTO.com/GetList", [Headers=[Authorization="Bearer " & GetAccessToken()]])),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{4}[Value]
in
    Value
in
  Query
========================================================
FAT_Table:
let
    Source = GetList(),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
========================================================


2. Example Full

let 
    tenantId = "435435435345435435",
    clientId = "34543543534535345543",
    clientSecret = "35435435435435435435",
    grantType = "client_credentials",
    resource = "https://graph.windows.net/",
    endpointUsers = "/users",
    apiVersion = "?api-version=1.6",
    baseURL="https://login.microsoftonline.com",
    relativePath = "/"&tenantId&"/oauth2/token",
    url = baseURL & relativePath,
   body2 = [
          client_id=clientId,
          grant_type=grantType,
          client_secret=clientSecret,
          resource=resource],
    Source = Json.Document(Web.Contents(url, [Headers=[#"Accept" = "application/json", #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],Content = Text.ToBinary(Uri.BuildQueryString(body2))])), 
    accessToken = Source[access_token],
    tokenType = Source[token_type],
    usersURL = tenantId & endpointUsers &apiVersion,
    fullAccessToken = tokenType&" "&accessToken,
    Users = Json.Document(Web.Contents(resource & tenantId & endpointUsers &apiVersion,[Headers=[ContentType="application/json", Authorization=fullAccessToken, RelativePath=usersURL]])),
    value = Users[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "ageGroup", "assignedLicenses", "assignedPlans", "city", "companyName", "consentProvidedForMinor", "country", "createdDateTime", "creationType", "department", "dirSyncEnabled", "displayName", "employeeId", "facsimileTelephoneNumber", "givenName", "immutableId", "isCompromised", "jobTitle", "lastDirSyncTime", "legalAgeGroupClassification", "mail", "mailNickname", "mobile", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "refreshTokensValidFromDateTime", "showInAddressList", "signInNames", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaEditLink", "usageLocation", "userIdentities", "userPrincipalName", "userState", "userStateChangedOn", "userType"}, {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "ageGroup", "assignedLicenses", "assignedPlans", "city", "companyName", "consentProvidedForMinor", "country", "createdDateTime", "creationType", "department", "dirSyncEnabled", "displayName", "employeeId", "facsimileTelephoneNumber", "givenName", "immutableId", "isCompromised", "jobTitle", "lastDirSyncTime", "legalAgeGroupClassification", "mail", "mailNickname", "mobile", "onPremisesDistinguishedName", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "refreshTokensValidFromDateTime", "showInAddressList", "signInNames", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaEditLink", "usageLocation", "userIdentities", "userPrincipalName", "userState", "userStateChangedOn", "userType"})
in
    #"Expanded Column1"


========================================================


3. example with Body parameters

let

    url = "https://j?????????????????",

    body  = "{ ""hCreationDate"": { ""$gte"": ""2021-08-01T00:00:00.000Z"",""$lte"": ""2021-08-03T00:00:00.000Z"" } } ",

    vData = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json" , Authorization="Bearer " & GetAcessToken()], Content=Text.ToBinary(body)])),

    results = vData[results],

    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"_id", "action", "feedId", "segment", "userId", "hCreationDate"}, {"_id", "action", "feedId", "segment", "userId", "hCreationDate"})

in 

    #"Expanded Column1"





No comments:

Post a Comment

Power BI APP - Delete or Unpublish

 UNPUBLISH DELETE