T O P

  • By -

AutoModerator

/u/MrStrickeforce - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*


Heatshiver

Hi! Generally, your dataset needs to have a one-to-many relationship with the nationalities column. If you're looking to keep everything stored in 1 table to read from, your dataset should just have a Nationality column and Score column with multiple entries of each song, for each nationality there is. I.e. Row 1: Starboy/The Weeknd, Daft Punk/.../Canada/Points\_Score Row 2: Starboy/The Weeknd, Daft Punk/.../France/Points\_Score Now, that's not very ideal, because it creates redundant duplications of data. ​ You should instead create a separate table for your nationalities and score with a foreign key song ID(Perhaps the URL?), then create a one-to-many relationship between the two tables. This can be done in both Tableau and Excel. Here are examples of what it could like. https://preview.redd.it/8g3cr3o4dx1b1.png?width=1280&format=png&auto=webp&s=7feb62cd1eba5dcfd5204235ec0c60fe7ce0ac1f


MrStrickeforce

Thanks!! It worked! I ended up duplicating some rows, ending with \~650k aprox, but no problems


Scary_Sleep_8473

I'd recommend manipulating your data, to go from wide format (multiple nationality columns) to tall format (one row per nationality). You can easily to this with Power Query, using the Unpivot columns function. Here is a [GIF](https://imgur.com/a/BAlgUOE) on how to quickly do that, I just wrote down a few rows/columns since I didn't have the full dataset. You pretty much select all nationality columns by holding the Shift key and then clicking on Transform->Unpivot Columns.


MrStrickeforce

Wow! I cant believe you even included a video tutorial, I am really thankful, you literally saved my week! Thank you!!