Forum Discussion
Display several fields in same map visualization
- 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
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