Search code examples
jsonpowerbim

Get table of map keys of JSON shape map visual in Power BI

Is there any easy way to get table of the Map Keys of a map shape visual? I would like to get the map keys as a table of Power Query. The best would be to extract it from JSON file from which the map as been imported.

Download file: NZ.json

enter image description here

Solution

  • Drill down on objects and geometries in the query editor, convert to a table, and expand the properties column:

    let
    Source = Json.Document(File.Contents("C:\Users\aolson\Downloads\NZ.json")),
    objects = Source[objects],
    gadm36_NZL_1 = objects[gadm36_NZL_1],
    geometries = gadm36_NZL_1[geometries],
    ConvertToTable = Table.FromList(geometries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandColumn = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"properties"}, {"properties"}),
    ExpandProperties = Table.ExpandRecordColumn(ExpandColumn, "properties", {"GID_0", "NAME_0", "GID_1", "NAME_1", "VARNAME_1", "NL_NAME_1", "TYPE_1", "ENGTYPE_1", "CC_1", "HASC_1"}, {"GID_0", "NAME_0", "GID_1", "NAME_1", "VARNAME_1", "NL_NAME_1", "TYPE_1", "ENGTYPE_1", "CC_1", "HASC_1"})
in
    ExpandProperties

    If you want it a bit more dynamic, replace the single ExpandProperties formula line with these two:

    ColumnNames = Record.FieldNames(ExpandColumn[properties]{0}),
ExpandProperties = Table.ExpandRecordColumn(ExpandColumn, "properties", ColumnNames, ColumnNames)