Power Query Example

Owned by Daniel Finn
Last updated: Apr 11, 2023

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.

Document Example

let
    cxApi = "https://au.itwocx.com/api/latest",
    cxUser = "example.user@email.com",
    cxPass = "****encryptedPass****",
    cxProjectName = "****projectName****",
    cxFormCode = "GEN",         
 	cxLoginRespose = Json.Document(Web.Contents(cxApi & "/Api/Login/ByEmail", 
        [
            Headers = [#"Content-Type"="application/json"],
            Content = Json.FromValue([ 
                email = cxUser,
                encryptedPassword = cxPass
            ])
        ]
    )),     
    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

Claim Example

let
    cxApi = "https://au.itwocx.com/api/latest",
    cxUser = "example.user@email.com",
    cxPass = "****encryptedPass****",
    cxProjectName = "****projectName****",
    cxLoginRespose = Json.Document(Web.Contents(cxApi & "/Api/Login/ByEmail",
        [
            Headers = [#"Content-Type"="application/json"],
            Content = Json.FromValue([
                email = cxUser,
                encryptedPassword = cxPass
            ])
        ]
    )),    
    cxKey = cxLoginRespose[Key],
    cxDataSource = Json.Document(Web.Contents(cxApi & "/Api/" & cxProjectName & "/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"


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"})