Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

Data organization for multiple vendors

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.  

Not applicable

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?

Not applicable

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. 

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.