Multiple Variables in Column. Split or Keep?

[nkhalqi]

Hello,

I am new to using power BI. I'm having trouble with multiple variables in one column.

My dataset below is for 100 companies. There are over 150 columns to use for analysis but some of these columns have multiple variables in one field. For example, about 50% of the compmanies operate in more than one state as you can see below in the "Operating_States_c".

I am trying to figure out what would be the best way to structure/clean the data to get an accurate visualization and analysis. Should I split the data by delimiter to columns? If I do, would the rest of the data (eg. revenue) be counted more than once? 

If I don't need to split it, how can I make PBI recognize these fields as multi-variables? Meaning, if I were to create a slicer with all 50 states, I want to make sure that all companies that operate in NY appear even if they operate in other states in addition to NY.

[Greg_Deckler]

In my opinion, you should create a separate table for companies and the states they operate in most likely, split out the states column and unpivot so that you have something like:

CompanyA, OH

CompanyA, IN

CompanyA, WI

CompanyB, OH

...

I would also create a table of distinct companies. Your third table would be your fact table and would have all of the columns except the State column. Then, relate your distinct company table to your other two tables in the obvious way.