Simple data cleaning (split columns that contain multiple values)/data modelling question

[Anonymous]

Hello everybody,

I frequently run into the same issue that I have a solution for but I wonder if there are better ways or best practices. Image one column contains several values per row. That's an easy fix through using "split by..." in Power Query and creating a new row for each value. However, the new row contains all the values of the other columns. Hence, if at one point I want to find the simple sum of another column that contains integers, I cannot simply sum them anymore (due to the duplicate rows) - instead, I have to build a measure.

This is some random sample data:

Color	ID	Value	Location
green, blue, yellow	75c692ab-b0fd-4866-a441-5d8d2034daed	34	New York
green, blue	6e3986c5-0b6f-4c6f-b6e3-c1bf8ac97e24	5	Denver
red	a208e7fa-b2b7-4807-8006-7f5cb6ea7720	34	Miami
red	27d834dd-ac11-4e2c-8834-727223a752ec	87	Denver
red	2cb56594-472e-4672-9375-0c77426dc95b	54	New York
red, blue	933e09ea-7b34-4c40-8a9f-7da18ba89eee	23	Miami
green, blue, red	ec45ecab-b652-41b9-a2ac-91cd855e7a62	3	Miami
yellow	ebb9df48-24ef-4070-bff3-bd145387b8a0	4	New York
green, blue, red, yellow	c9fc4929-866c-4c0f-902b-741117ee3e64	534	Denver

The sum of value is 778 but after splitting the color columns, the simple sum without measure is 2482.

This is what the data model would look like after splitting the color column and here is the sample file: https://drive.google.com/file/d/1j6lkP4BMHWOpzq90NfbS7HaFzQPJAEmr/view?usp=sharing

Thanks a lot, in advance!

[jdbuchanan71]

@Anonymous 

If I am understand your question, your model kind of does what I would suggest.  Your color table only needs to contain the color, split into a row for each like you have it, and the ID.  Then you link it to your upload table, like you have it with bi-directional.  Then you can sum the value from the upload table and show the breakdown by color from the color table.

[Anonymous]

Ha! And then, if a wanted to add a slicer for the color, I would add it from the color table.

In general, I can have however many dimension tables, and combine it with the value column from the original upload table, right?

Ok, I was close. Thank you 🙂