Power Query Example

Using Power BI Desktop

New, Get Data, Blank Query, Advanced Editor, and enter the follow query and replace the user, pass, project name and possibly the form code.

Login Function Example

This example uses parameters:

CX_API: https://au.itwocx.com/api/latest
CX_Email: example.user@email.com
CX_EncryptedPassword: ***encryptedPassword***
CX_ProjectName: ***projectName***

Function: Login_ByEmail

() => let 
        loginByEmail = Json.Document(Web.Contents(CX_API & "/Api/Login/ByEmail",
            [
                Headers = [#"Content-Type"="application/json"],
                Content = Json.FromValue([
                    email = CX_Email,
                    encryptedPassword = CX_EncryptedPassword
                ])
            ]
        )),
        key = loginByEmail[Key]
    in
        key

Document Query Example

let
    cxKey = Login_ByEmail(),
    cxFormCode = "GEN",
    cxDataSource = Json.Document(Web.Contents(CX_API & "/Api/" & CX_ProjectName &"/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

Claim Example

let
    cxKey = Login_ByEmail(),
    cxDataSource = Json.Document(Web.Contents(CX_API & "/Api/" & CX_ProjectName & "/Claim/Get?take=1000", 
        [
            Headers = [#"key" = cxKey]
        ]
    )),
    #"Converted to Table" = Table.FromList(cxDataSource[Items], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Claim"}}),
    #"Expanded Claim" = Table.ExpandRecordColumn(#"Renamed Columns", "Claim", {"ContractId", "LineItems", "RetentionOnClaimed", "RetentionOnScheduled", "TotalClaimed", "TotalScheduled", "Id", "DocCode", "DocTypeCode", "Reference", "ProjectName", "StatusName", "Title", "AuthorCode", "CreatedDate", "IssuedDate", "DueDate", "ClosedDate", "LastClosedDate", "OpenedDate", "LastChangedDate", "LastModifiedDate", "ActionCodes", "InfoCodes", "UserfieldValues", "Attachments", "DocSettingId", "StatusId", "Timestamp", "IsPrivate", "LinkedDocuments"}, {"Claim.ContractId", "Claim.LineItems", "Claim.RetentionOnClaimed", "Claim.RetentionOnScheduled", "Claim.TotalClaimed", "Claim.TotalScheduled", "Claim.Id", "Claim.DocCode", "Claim.DocTypeCode", "Claim.Reference", "Claim.ProjectName", "Claim.StatusName", "Claim.Title", "Claim.AuthorCode", "Claim.CreatedDate", "Claim.IssuedDate", "Claim.DueDate", "Claim.ClosedDate", "Claim.LastClosedDate", "Claim.OpenedDate", "Claim.LastChangedDate", "Claim.LastModifiedDate", "Claim.ActionCodes", "Claim.InfoCodes", "Claim.UserfieldValues", "Claim.Attachments", "Claim.DocSettingId", "Claim.StatusId", "Claim.Timestamp", "Claim.IsPrivate", "Claim.LinkedDocuments"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Claim", {"Claim.ContractId", "Claim.RetentionOnClaimed", "Claim.RetentionOnScheduled", "Claim.TotalClaimed", "Claim.TotalScheduled", "Claim.Id", "Claim.DocCode", "Claim.DocTypeCode", "Claim.Reference", "Claim.ProjectName", "Claim.StatusName", "Claim.Title", "Claim.AuthorCode", "Claim.CreatedDate", "Claim.IssuedDate", "Claim.DueDate", "Claim.ClosedDate", "Claim.LastClosedDate", "Claim.OpenedDate", "Claim.LastChangedDate", "Claim.LastModifiedDate"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Claim.LastClosedDate", type datetime}, {"Claim.CreatedDate", type datetime}, {"Claim.IssuedDate", type datetime}, {"Claim.DueDate", type datetime}, {"Claim.ClosedDate", type datetime}, {"Claim.OpenedDate", type datetime}, {"Claim.LastChangedDate", type datetime}, {"Claim.LastModifiedDate", type datetime}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",#datetime(1, 1, 1, 0, 0, 0),null,Replacer.ReplaceValue,{"Claim.CreatedDate", "Claim.IssuedDate", "Claim.DueDate", "Claim.ClosedDate", "Claim.LastClosedDate", "Claim.OpenedDate", "Claim.LastChangedDate", "Claim.LastModifiedDate"})
in
    #"Replaced Value"

Saved Search Example

let
    cxKey = Login_ByEmail(),
    savedSearchId = ***savedSearchId***,
    excelData = Json.Document(Web.Contents(CX_API & "/Api/" & CX_ProjectName & "/SavedSearch/GetExcelData/" & savedSearchId,
            [
                Headers = [#"key" = cxKey]
            ]
        )),
    excelTable = Table.FromRows(excelData[Rows], excelData[Columns]),
    columnTypes = Table.TransformColumnTypes(excelTable,{{"Issued", type datetime},{"Modified", type datetime}})
in
    columnTypes

Including a User Field value

As there can be many user field values depending on configuration, you need to filter to the relevant one you are interested in:

e.g. on the Defect form there is a Trade field, which can be included as follows:

ufTrade = Table.ExpandRecordColumn(Table.AddColumn(cxResult, "TradeRecord", each List.First( List.Select([UserfieldValues], each [UserfieldCode] = "TRADE"))), "TradeRecord", {"Value"}, {"Trade"})