/
Power Query Example

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.

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

Related content

Power Query with Pagination
Power Query with Pagination
More like this
Power Query with SSO Login Example
Power Query with SSO Login Example
More like this
Power BI
More like this
Documents
More like this
Users and Companies
Users and Companies
More like this
20.03 Update 15-Apr-2020
20.03 Update 15-Apr-2020
More like this