Skip to main content
cancel
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.

Reply
wright2fish
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.  

3 REPLIES 3
Anonymous
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.

Brenden_0-1694713574670.png

Brenden_1-1694713589374.png

 

I connected those tables in relationship view where StateShort and ContractorState (also short) are matched up.

Brenden_2-1694713645873.png

 

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.

Brenden_3-1694713798835.png

 

Brenden_4-1694713846708.png

 



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?

Anonymous
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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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