Power Query with SSO Login Example

After setting up iTWO cx for Single Sign On (SSO) - Azure AD

And using the same process as Power Query Example

The advanced query can be varied to authenticate to Azure AD first, getting an access_token and using that to authenticate to the REST API using the Login/ByUserToken interface.

let
    tennantId = "*** Your Azure AD Tennant ID ***",
    clientId = "*** Your Azure AD iTWO cx Application ID ***",
    token_uri = "https://login.microsoftonline.com/" & tennantId & "/oauth2/token",
    cxResource = "52e5f8a4-8b0e-455f-9df4-5beb7c37dd18",
    tokenResponse = Json.Document(Web.Contents(token_uri,
    [
        Content = Text.ToBinary(Uri.BuildQueryString(
            [
                client_id = clientId,
                resource = cxResource,
                grant_type = "password",
                username = "*** Your Azure AD user name / email (e.g. text.user@itwocx.onmicrosoft.com) ***",
                password = "*** Password ***"
            ]
        )),
        Headers = [Accept = "application/json"], ManualStatusHandling = {400}
    ])),
    access_token = tokenResponse[access_token],
    cxApi = "https://au.itwocx.com/api/latest",   
    cxProjectName = "PSDEMO",
    cxFormCode = "TR",
    cxLoginRespose = Json.Document(Web.Contents(cxApi & "/Api/Login/ByUserToken", 
        [
            Content = Text.ToBinary(""),
            Headers = [Authorization = "Bearer " & access_token]
        ])),
    cxKey = cxLoginRespose[Key],
    cxDataSource = Json.Document(Web.Contents(cxApi & "/Api/" & cxProjectName &"/Document/GetByDocCode?code=" & cxFormCode & "&take=1000",
        [
            Headers = [#"key" = cxKey]
        ]
    )),
    cxTable = Table.FromList(cxDataSource[Items], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    cxResult = Table.ExpandRecordColumn(cxTable, "Column1", {"Id", "DocCode", "DocTypeCode", "Reference", "ProjectName", "StatusName", "StatusType", "Title", "AuthorCode", "AuthorCompanyCode", "CreatedDate", "IssuedDate", "DueDate", "ClosedDate", "LastClosedDate", "OpenedDate", "LastChangedDate", "LastModifiedDate", "FirstActionCompanyCode", "ActionCodes", "InfoCodes", "UserfieldValues", "Attachments", "Comments", "DocSettingId", "StatusId", "Timestamp", "IsPrivate", "LinkedDocuments"}, {"Column1.Id", "Column1.DocCode", "Column1.DocTypeCode", "Column1.Reference", "Column1.ProjectName", "Column1.StatusName", "Column1.StatusType", "Column1.Title", "Column1.AuthorCode", "Column1.AuthorCompanyCode", "Column1.CreatedDate", "Column1.IssuedDate", "Column1.DueDate", "Column1.ClosedDate", "Column1.LastClosedDate", "Column1.OpenedDate", "Column1.LastChangedDate", "Column1.LastModifiedDate", "Column1.FirstActionCompanyCode", "Column1.ActionCodes", "Column1.InfoCodes", "Column1.UserfieldValues", "Column1.Attachments", "Column1.Comments", "Column1.DocSettingId", "Column1.StatusId", "Column1.Timestamp", "Column1.IsPrivate", "Column1.LinkedDocuments"})
in
    cxResult

This example uses username and password. Any supported microsoft method to get an oauth2 token could be substituted in place of this. As long as the token is for the iTWOcx resouce, and lists the username matching the email address in iTWO cx it can be used. You can verify the contents of the token here: https://jwt.io/

This example works with a non Federated user account where Azure AD is providing the OAuth2 token. If the account uses ADFS / Federation, you will need to follow a different authentication flow from Microsoft to get the token.

You would set the authentication to anonymous for login.microsoft.com:

You can check you got an access_token:

And that it authenticated to iTWO cx:

While ends up with a result based on the API request: