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
lettenantId = "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