To help clients getting started in querying data from Vitruvi APIs, the following functions have been provided.

Function 1: fVitruviPagedQuery

The first method is used when querying data from the RAW or _Query APIs, as described in the API documentation.

The function performs a number of functions including paging the results, applying filters, applying field selections and automatically renaming fields to proper display names.

The query function requires 3 additional parameters to be defined in Power Query (Note: these are case sensitive):

TEST_MODE - (Boolean) When set to TRUE, the query functions will only retrieve 1 page of data from the API. This is useful when doing initial development of the Power Query model to speed up the calls to the API and only retrieve data required to explore the APIs model, debug or fix errors and fine tune the field selection and data filtering parameters. Set to FALSE when the model is intended to go into production to retrieve all pages of results.
WorkspaceURL - (Text) The URL of the API for your workspace. Usually in the format: "https://MY_WORKSPACE.api.vitruvi.cc/".
Token - (Text) The API security token. If you do not have a token, one will need to be requested from your Vitruvi representative.

(
    ApiPath as text,
    EntitiesPerPage as number,
    optional FilterCriteria as nullable record,
    optional FieldList as nullable list,
    optional DisplayNameList as nullable list
  ) => 
  let
    
    /**********************************************************************
     *  Note: This function assumes there is are 2 parameters already 
     *  defined - `WorkspaceURL` with the full path to the workspace URL
     *  (i.e. https://myWorkspace.api.vitruvi.cc) and `Token` with the 
     *  system generated auth token string.
     * 
     * If these two parameters do not exist or are named differently, the
     * lines below must be modified accordingly.
    ***********************************************************************/
    
    BaseURL = WorkspaceURL,
    AuthToken = "Bearer " & Token,
    
    /**********************************************************************/


    FieldSelection  = 
        if FieldList = null then
          null
        else
          if List.Count(FieldList) = 0 then
            null
          else
            Text.Combine(FieldList,","),
    Fields = if FieldList = null then null else [fields=FieldSelection],
    
    //some helper functions to call iteratively
    GetJson = (postData) =>
        let 
            Options = [
                        RelativePath=ApiPath,
                        Headers=[ #"Authorization" = AuthToken, Accept="application/json", #"X-HTTP-Method-Override"="GET", #"Content-Type"="application/x-www-form-urlencoded"],
                        Content=Text.ToBinary(Uri.BuildQueryString( postData ))
                      ],
            RawData = Web.Contents(BaseURL, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetOnePage = (QueryParams) as record =>
        let
            Source  = GetJson(QueryParams),
            data    = try Source[results] otherwise null,
            next    = try Source[next] otherwise null,
            res     = [Data=data, Next=next]
        in res,
    
    GetQueryParams = (Index) =>
        let 
            Offset  = [offset=Text.From(Index * EntitiesPerPage)],
            Limit   = [limit=Text.From(EntitiesPerPage)],
            Param = Record.Combine(
                                    List.RemoveNulls(
                                      {Limit, Offset, Fields, FilterCriteria}
                                    )
                                  ),
            ParamAsJson = Json.FromValue(Param)
        in Param,
    
    AddColumnFromList = ( targetTable as table, nameForColumn as text, listForColumnValues as list) as table =>
        let 
            tempTable = Table.FromColumns({listForColumnValues}, {nameForColumn}),
            tempTableWithIndex = Table.AddIndexColumn(tempTable, "joinIndex", 0, 1),
            targetTableWithIndex = Table.AddIndexColumn(targetTable, "joinIndex", 0, 1),
            joinedTable = Table.Join(targetTableWithIndex , "joinIndex", tempTableWithIndex , "joinIndex", JoinSide.Left),
            joinedTableWithoutIndex = Table.RemoveColumns(joinedTable, "joinIndex")
        in 
            joinedTableWithoutIndex,

    Pages =
        List.Generate(
            //  initial function
            () => [
                i=0, 
                lastPage = false, 
                res = GetOnePage(GetQueryParams(i))
            ],                                                      
            
            //  condition function
            each [res][Next] <> null or not [lastPage],              
            
            //  next function
            each [
                i=[i]+1, 
                lastPage = if [res][Next] = null then true else false, 
                res = GetOnePage(GetQueryParams(i))
            ],    
            
            //  selector
            each [res][Data]                                        
        ),

    NoData    = List.First(Pages) = null,
    CleansedPages   = 
        if NoData then
          {{[
            Data = "No Data Available. Please check that the URL and authorization token are correct and the filters are appropriate."
          ]}}
        else
          Pages,
    Entities  = List.Union(CleansedPages),
    Table     = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedFieldList = 
        if FieldList = null or List.Count(FieldList) = 0 or NoData then
          Record.FieldNames( Table{0}[Column1] )
        else
          FieldList,
    ExpandedDisplayNameList =
        if DisplayNameList = null or List.Count(DisplayNameList) = 0 or NoData then
          ExpandedFieldList
        else
          DisplayNameList,
    ExpandedJSON= Table.ExpandRecordColumn(Table, "Column1", ExpandedFieldList, ExpandedDisplayNameList)

in
    ExpandedJSON

This function takes 2 manditory arguments

APIPath - the path to the RAW endpoint that is being queried (see Raw API Models) or the path to the _Query endpoint (see Available APIs).
EntitiesPerPage - the number of records per page to return (see API Paging).

There are 3 additional optional arguments

FilterCriteria - a record containing the filters to send to the endpoint (see Filtering). Set to null if no filter is to be used.
FieldList - a list containing the names of the fields returned by the API. Effectively the SELECT part of the query. Set to null to return all fields.
DisplayNameList - a list containing the display names corresponding to the fields provided in the FieldList. This helps save a step in the process by making it easyier to rename fields automatically in the function. Set to null to default the display names to their original field names.

Function 2: fFlattenHierarchy

Regions in Vitruvi are stored in a hierarchical table which requires recursion to get both the current level as well as all parent levels into a table format.

Reporting tools typically do not work well with hierarchical or recursive data. However, it is often necessary to flatten the data into a tabular format in reports and analytic tools for proper grouping and sorting of the data.

This function can be used to flatten the regions into a tabular format which can then be linked or joined to any other table. Although this function can be used for any data from any database that is in a recursive hierarchical format, the regions are the only data model where this occurs.

//Credit & thanks to https://pivotalbi.com/dynamically-flatten-a-parent-child-hierarchy-using-power-query-m/ for this function

let 
  fFlattenHierarchy = (
    hierarchyTable as table
    ,parentKeyColumnIdentifier as text
    ,parentNameColumnIdentifier as text
    ,childKeyColumnIdentifier as text
    ,childNameColumnIdentifier as text
  ) as table =>
  let


    #"Get Root Parents" = Table.Distinct(
      Table.SelectColumns(
        Table.NestedJoin(hierarchyTable
          ,parentKeyColumnIdentifier
          ,hierarchyTable
          ,childKeyColumnIdentifier
          ,"ROOT.PARENTS"
          ,JoinKind.LeftAnti
        )
        ,{
          parentKeyColumnIdentifier
          ,parentNameColumnIdentifier
        }
      )
    ),
    #"Generate Hierarchy" = fGetNextHierarchyLevel(
      #"Get Root Parents"
      ,parentKeyColumnIdentifier
      ,1
    ),


    fGetNextHierarchyLevel = (
      parentsTable as table
      ,nextParentKeyColumnIdentifier as text
      ,hierarchyLevel as number
    ) =>
      let 
        vNextParentKey = childKeyColumnIdentifier & Text.From(hierarchyLevel),
        vNextParentName = childNameColumnIdentifier & Text.From(hierarchyLevel),
        #"Left Join - hierarchyTable (Get Children)" = Table.NestedJoin(parentsTable
          ,nextParentKeyColumnIdentifier
          ,hierarchyTable
          ,parentKeyColumnIdentifier
          ,"NODE.CHILDREN"
          ,JoinKind.LeftOuter
        ),
        #"Expand Column - NODE.CHILDREN" = Table.ExpandTableColumn(#"Left Join - hierarchyTable (Get Children)"
          ,"NODE.CHILDREN"
          ,{
              childKeyColumnIdentifier
              ,childNameColumnIdentifier
          },{
              vNextParentKey
              ,vNextParentName
          }
        ),
        #"Filter Rows - Parents with Children" = Table.SelectRows(#"Expand Column - NODE.CHILDREN"
          ,each Record.Field(_,vNextParentKey) <> null 
              and Record.Field(_,vNextParentKey) <> Record.Field(_,nextParentKeyColumnIdentifier)
        ),
        #"Generate Next Hierarchy Level" = if Table.IsEmpty(#"Filter Rows - Parents with Children")
          then parentsTable 
          else Table.Combine(
            {
              parentsTable
              ,@fGetNextHierarchyLevel(
                #"Filter Rows - Parents with Children"
                ,vNextParentKey
                ,hierarchyLevel + 1
              )
            }
        )
      in 
        #"Generate Next Hierarchy Level",


    #"Add Column - HierarchyPath" = Table.AddColumn(#"Generate Hierarchy",
      "HierarchyPath"
      ,each Text.Combine(
        List.Transform(
          Record.FieldValues(
            Record.SelectFields(_,
              List.Select(Table.ColumnNames(#"Generate Hierarchy")
                ,each Text.StartsWith(_,childKeyColumnIdentifier) 
                  or Text.StartsWith(_,parentKeyColumnIdentifier)
              )
            )
          )
          ,each Text.From(_)
        )
        ,"|"
      )
      ,type text
    ),
    #"Add Column - HierarchyNodeID" = Table.AddColumn(#"Add Column - HierarchyPath",
      "HierarchyNodeID"
      ,each List.Last(Text.Split([HierarchyPath],"|"))
      ,type text
    ),
    #"Add Column - HierarchyLevel" = Table.AddColumn(#"Add Column - HierarchyNodeID",
      "HierarchyLevel"
      ,each List.Count(Text.Split([HierarchyPath],"|"))
      ,Int64.Type
    ),
    #"Add Column - IsLeafLevel" = Table.AddColumn(#"Add Column - HierarchyLevel",
      "IsLeafLevel"
      ,each List.Contains(
          List.Transform(
            Table.Column(
              Table.NestedJoin(hierarchyTable
              ,childKeyColumnIdentifier
              ,hierarchyTable
              ,parentKeyColumnIdentifier
              ,"LEAFLEVEL.CHILDREN"
              ,JoinKind.LeftAnti
              )
              ,childKeyColumnIdentifier
            )
            ,each Text.From(_)
          )
        ,List.Last(Text.Split([HierarchyPath],"|"))
      )
      ,type logical
    )
  in
    #"Add Column - IsLeafLevel",


  //Documentation
  fFlattenHierarchyType = type function (
    hierarchyTable as (type table meta [
      Documentation.FieldCaption = "Hierarchy"
      ,Documentation.LongDescription = "A table containing a parent-child hierarchy"
      ]
    )
    ,parentKeyColumnIdentifier as (type text meta [
      Documentation.FieldCaption = "Parent Key Column Identifier"
      ,Documentation.LongDescription = "The name of the column used to identify the key of the parent node in the hierarchy"
      ,Documentation.SampleValues = { "ParentID" }
      ]
    )
    ,parentNameColumnIdentifier as (type text meta [
      Documentation.FieldCaption = "Parent Name Column Identifier"
      ,Documentation.LongDescription = "The name of the column used to identify the name of the parent node in the hierarchy"
      ,Documentation.SampleValues = { "ParentName" }
      ]
    )
    ,childKeyColumnIdentifier as (type text meta [
      Documentation.FieldCaption = "Child Key Column Identifier"
      ,Documentation.LongDescription = "The name of the column used to identify the key of the child node in the hierarchy"
      ,Documentation.SampleValues = { "ChildID" }
      ]
    )
    ,childNameColumnIdentifier as (type text meta [
      Documentation.FieldCaption = "Child Name Column Identifier"
      ,Documentation.LongDescription = "The name of the column used to identify the name of the child node in the hierarchy"
      ,Documentation.SampleValues = { "ChildName" }
      ]
    )
  ) as list meta [
    Documentation.Name = "fFlattenHierarchy"
    ,Documentation.LongDescription = "Returns a flattened hierarchy table from a parent-child hierarchy table input."
      & "The number of columns returned is based on the depth of the hierarchy. Each child node will be prefixed"
      & "with the value specified for the childNameColumnIdentifier parameter"
    ,Documentation.Examples = {
      [
      Description = "Returns a flattened hierarchy table from a parent-child hierarchy table"
      ,Code = "fFlattenHierarchy(barksdaleOrganisation, ""ParentNodeID"", ""ParentNodeName"", ""ChildNodeID"", ""ChildNodeName"")"
      ,Result = "{100,2,3,51,62,""Stringer"",""Shamrock"",""Slim Charles"",""Bodie"",""Pudding"",5,""100|2|3|51|62"",TRUE,62}"
        & ",{100,2,3,51,""Stringer"",""Shamrock"",""Slim Charles"",""Bodie"",4,""100|2|3|51"",FALSE,51}"
      ]
    }
  ]
in
  Value.ReplaceType(fFlattenHierarchy, fFlattenHierarchyType)