How to create filled map more than one province on single filter

[hariepermana]

Hi Experts,

My company has a many area covered. i wanna make a filled map, when i klik one of head office those filled map show more than 1 province/state..

here my simple data

HEAD OFFICE | AREA/PROVINCE COVERED

---------------  ------------------------------
PALEMBANG  | SOUTH SUMATRA & BANGKA BELITUNG
LAMPUNG      | LAMPUNG

JAMBI             | JAMBI & BENGKULU
example : when i klik palembang, those filled map show south sumatra province and bangka belitung province

i appreciate for every help

[Anonymous · Accepted solution]

Hi @hariepermana ,

You can make some transformations for the table data by the following steps, then create a filled map based on new Value field:

1. Split the column value by delimiter "&"

2. Unpivot these split area/province columns

3. Remove Attribute column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnD0cfV1cvRzP7RAQUlHSSHYPzTEQyE41NcxJMhRIabUwMDITAEk7+2o4OTq4xkS6ueuFKsTreTj6BsQCtYGQSDNUDGwvJejr5MnXBYJgRSCJeGmuwJND/UJVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"HEAD OFFICE " = _t, #" AREA/PROVINCE COVERED" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HEAD OFFICE ", type text}, {" AREA/PROVINCE COVERED", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", " AREA/PROVINCE COVERED", Splitter.SplitTextByDelimiter("&", QuoteStyle.Csv), {" AREA/PROVINCE COVERED.1", " AREA/PROVINCE COVERED.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{" AREA/PROVINCE COVERED.1", type text}, {" AREA/PROVINCE COVERED.2", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {" AREA/PROVINCE COVERED.2", " AREA/PROVINCE COVERED.1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"})
in
    #"Removed Columns"

Best Regards

Rena

[amitchandak]

@hariepermana , refer if this can help

https://radacad.com/shape-map-better-than-the-filled-map

https://doc.arcgis.com/en/maps-for-powerbi/get-started/prepare-your-data.htm

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map