Reporting on Regions

As referenced in Power Query Vitruvi Functions, the regions data model in Vitruvi is recursive - which means that the data in the table refers to itself. This is an effecient method of storage for database applications, however it often makes it difficult to work with in reporting and analytic applications.

To assist with this, the documented fFlattenHierarchy in the document above will transform the the regions data model into a flattened table with all child nodes. The HierarchyNodeID in the resulting table can be used to link to any other table from the Vitruvi data model where you see a field for region_id.

As the fFlattenHierarchy function is for use by all clients with different region level configurations, the region levels are generically named child_name1, child_name2, etc. This will have to be suitably renamed for the hierarchy levels defined for your workspace and network operator.

Example - Flattening the Regions Hierarchy

let 
    // fields to get from the regions table
    FieldList = {"id","name","region_level_id","network_operator_id","parent_region_id"},
    Regions = fVitruviPagedQuery("/api/v1/raw/network_operator/region/", 5000, null, FieldList, null),
    RegionTypeConvertion = Table.TransformColumnTypes(Regions,{{"id", Int64.Type}, {"name", type text}, {"region_level_id", Int64.Type}, {"network_operator_id", Int64.Type}, {"parent_region_id", Int64.Type}}),

    // get a table containing the region data (that will represent the "child" regions
    ChildRegions = Table.RenameColumns(Regions,{{"id", "child_id"}, {"name", "child_name"}}),

    // merge the parent and child tables
    MergedParentRegions = Table.NestedJoin(ChildRegions, {"parent_region_id"}, RegionTypeConvertion, {"id"}, "MergedParentRegions", JoinKind.LeftOuter),
    ExpandParentRegions = Table.ExpandTableColumn(MergedParentRegions, "MergedParentRegions", {"id", "name"}, {"parent_id", "parent_name"}), 

    // filter out the record where the parent ID is null
    RemoveEmptyNode = Table.SelectRows(ExpandParentRegions, each ([parent_region_id] <> null)),
    
    // now flatten the hierarchy
    FlattenHierarchy = fFlattenHierarchy(RemoveEmptyNode, "parent_id", "parent_name", "child_id", "child_name")

in
    FlattenHierarchy