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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nkhalqi
New Member

Multiple Variables in Column. Split or Keep?

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.

 

 

Capture.PNG

1 REPLY 1
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.