Forum Discussion
Display several fields in same map visualization
Hi all!
I'm stucked since a few days with a requeriment from a client. The issue is, they want to display in the same map visualization, several map points, which latitudes/longitudes I have in several fields in same row. Im not able to reach this result, could someone help me with some suggestion ?
My data looks like:
| Place | ORS_1_Latitude | ORS_1_Longitude | ORS_2_Latitude | ORS_2_Longitude | ORS_3_Latitude | ORS_3_Longitude |
| 1 | -45.591861 | -72.074389 | -40.744325 | -73.125101 | -39.80652 | -73.39631 |
| 2 | -39.156052 | -72.663498 | -38.334028 | -72.374917 | -38.33389 | -72.374887 |
And I need to display in same map, the ORS1, ORS2 and ORS3 points (when place 1 is selected in the filter, for example).
And if possible, a last question: is possible to display, in every point, the field name instead of latitude/longitude ? (this would be just a nice to have, the main requeriment is the first one)
Thanks in advance,
Regards
Hi, I copied the above dataset and did the below transformation in Power Query:
let
Source = Excel.Workbook(File.Contents("drive:\xxxx\xxx\xxxxxx\Map.xlsx"), null, true), /*put your location*/
Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Place", Int64.Type}, {"ORS_1_Latitude", type number}, {"ORS_1_Longitude", type number}, {"ORS_2_Latitude", type number}, {"ORS_2_Longitude", type number}, {"ORS_3_Latitude", type number}, {"ORS_3_Longitude", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Place"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "LocationName"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
#"Pivoted Column"Simply, first unpivot and then pivot to bring the table like below structure.
Once Done, select map visual and add Latitude and Longitude and in tooltip, add Location Name and add a slicer on Place column.
If this resolves your problem then please accept this as solution.
Also you may follow my blog page (https://littlebidata.wordpress.com/) where I share some technical knowledge and different case studies on Power BI. Thanks
2 Replies
Hi, I copied the above dataset and did the below transformation in Power Query:
let
Source = Excel.Workbook(File.Contents("drive:\xxxx\xxx\xxxxxx\Map.xlsx"), null, true), /*put your location*/
Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Place", Int64.Type}, {"ORS_1_Latitude", type number}, {"ORS_1_Longitude", type number}, {"ORS_2_Latitude", type number}, {"ORS_2_Longitude", type number}, {"ORS_3_Latitude", type number}, {"ORS_3_Longitude", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Place"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "LocationName"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
#"Pivoted Column"Simply, first unpivot and then pivot to bring the table like below structure.
Once Done, select map visual and add Latitude and Longitude and in tooltip, add Location Name and add a slicer on Place column.
If this resolves your problem then please accept this as solution.
Also you may follow my blog page (https://littlebidata.wordpress.com/) where I share some technical knowledge and different case studies on Power BI. Thanks