Forum Discussion
Two locations in one row on a single map?
I am working with data that has two important related locations in each row. I would like to have both locations dropped on a map in two different colors. I am unable to map more than 1 location to the map visual. Is there a way to do this while also maintaining the link between the map and related visuals?
I am attaching mock data that illustrates the issue. I would like both the Store and the Warehouse locations mapped on the map. I cannot use the Legend to differentiate them because they are different columns, which seems to be the common suggestion in the forum posts I searched.
| Item | Quantity | Warehouse | Store | Price |
| Sweater | 1 | Owensboro, KY | Lexington, KY | 20 |
| Jeans | 1 | Owensboro, KY | Louisville, KY | 30 |
| Shoes | 1 | Pikeville, KY | Louisville, KY | 25 |
| Sweater | 1 | Owensboro, KY | Bowling Green, KY | 20 |
| Jeans | 1 | Owensboro, KY | Bowling Green, KY | 30 |
| Sandals | 1 | Pikeville, KY | Covington, KY | 15 |
The above image shows only the Store locations being mapped. I basically just want to also map the Warehouse locations in red while also maintaining the the ability to click on a Warehouse in the table and have the entire page filter to items associated with that warehouse.
You achieve that by unpivoting your data to bring it into usable format
Then you can use the location type for the pin coloring.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5PTSxJLVLSUTIEYv/y1LzipPyifB0F70gg3ye1IjMvvSQ/D8o3MlCK1YlW8kpNzCvGpSW/NLO4LDMnJxUqYAzRE5yRnwrTE5CZnYqsBEOPkSlED17HOeWX5wBdp+BelJpKggOxaYO5MTEvJTEHlyud88tQAsMQ6MhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Quantity = _t, Warehouse = _t, Store = _t, Price = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item", "Quantity", "Price"}, "Location Type", "Location") in #"Unpivoted Other Columns"
2 Replies
- lbendlin
Super User
You achieve that by unpivoting your data to bring it into usable format
Then you can use the location type for the pin coloring.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5PTSxJLVLSUTIEYv/y1LzipPyifB0F70gg3ye1IjMvvSQ/D8o3MlCK1YlW8kpNzCvGpSW/NLO4LDMnJxUqYAzRE5yRnwrTE5CZnYqsBEOPkSlED17HOeWX5wBdp+BelJpKggOxaYO5MTEvJTEHlyud88tQAsMQ6MhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Quantity = _t, Warehouse = _t, Store = _t, Price = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item", "Quantity", "Price"}, "Location Type", "Location") in #"Unpivoted Other Columns"Unpivoting got me where I needed to be. I did not understand the code snippet fully, but searching other resources for unpivoting was helpful. Thank you so much!
