Forum Discussion

gg80's avatar
gg80
Frequent Visitor
2 years ago
Solved

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 s...
  • samratpbi's avatar
    2 years ago

    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

     



  • gg80's avatar
    gg80
    2 years ago

    HI samratpbi , thanks! Sorry for the delay in answer, today I will test your solution, sounds as a good approach. I will let u know and mark as solved if it works, thanks again!