Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I am trying to figure out how to organize by excel data based off of states and what general contarctors work in those states. I eventually want to create a map visual where I click on a state and the general contractors will be shown.
I would start with two tables.
1, all the states with a column of the state name, a column of the state short code.
2, all the contractors with a column of their name/company name, their state short code, address, any other details specific to them.
I connected those tables in relationship view where StateShort and ContractorState (also short) are matched up.
Then added a map visual with StateShort, as well as some Matrixes to list their names. You can see each state with Contractors has a dot on the map. When I click New York, the tables filter to New York and show only those contractors.
Just a rough idea how to begin. Hope this helps.
Thank you for your quick reply. For each contyractor, I ahve multiple states. How do I account for that on the excel sheet?
For the sake of keeping it organized and easier to manage in the future (and allow one-to-many relationships to work), I would do a table that is just a unique list of contractors (like the one above). Then make another table (third one now) that is something like "ServiceAreas" which will have one contractor & state per row, and it is ok to have multiple rows of the same contractor, one for each state they service.
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
44 | |
41 | |
39 | |
37 |