cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
Brenden
Advocate II
Advocate II

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?

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors