API using excel

13

Just wanted to share this updated Power Query connector function for Excel and Power BI.

let
    _getObjectIndex = (url as text, optional business as text, optional object as text) => 
        let 
            objectURL = url & 
                (if business <> null then "/" & business else "") & 
                (if object <> null then "/" & object else "") & ".json",
            result = try Json.Document(Web.Contents(objectURL)) otherwise null
        in
            result,

    _getObjectTable = (#"API Endpoint" as text, optional #"Business Key" as text, optional #"Object Key" as text) =>
        let    
            indexTable = Table.FromRecords(_getObjectIndex(#"API Endpoint", #"Business Key", #"Object Key")),
            mergedObject = if #"Object Key" <> null
            then
                Table.AddColumn(
                    indexTable, 
                    "Object", 
                    each Json.Document(
                        Web.Contents((#"API Endpoint" & "/" & #"Business Key" & "/" & #"Object Key" & "/" & [Key] & ".json"))
                    )
                )
            else
                indexTable
        in
            if Table.IsEmpty(mergedObject)
            then 
                Table.FromRecords({[Key =" ", Name = "" , Timestamp = 0, FormType = "", Object = "Error: No Records found!"]})
            else 
                try mergedObject otherwise 
                    Table.FromRecords({[Key =" ", Name = "" , Timestamp = 0, FormType = "", Error = "Error: Can't connect!"]}),

    getQueryTable = _getObjectTable
in
    getQueryTable

Everyone is welcome to share their own improvements to this code.

Enjoy!

2 Likes