How to create new rows from values of different columns with same ID

[Anonymous]

Hi All, 

my data is about movies which are played in different countries. Therefore I want to create a map vizualization which makes clear, in which countries the most movies are played (different sized bubbles)

 

                                   

My problem is, that in my data all the countries are separated by comma, so they are not counted each as an individual. My though was to split the columns and create new rows in a new query. (So e.g. 3 rows with the same ID, different countries). 

Unluckily I have no clue how to add the columns as rows and automatically adjust the right ID (I tried everything with unpivot/pivot/..)

Do you have an idea, how this could work? I'm also opened to any ideas how it could work with a different method to visualize the countries in different sized bubbles. 

Thank you so much in advance for your help!

Laura

[AlexisOlson]

You can split into rows instead of columns.

(This is a recycled screenshot. You'll want to split on the delimiter ", " instead of semicolon.)

[Anonymous]

Hi Alexis, 

vielen Dank für deine Hilfe!

Das Problem hierbei ist allerdins, dass neue IDs zu den jeweils neu hinzu gefügten Zeilen erstellt - das verfälscht dann die usprünglichen IDs. Fällt dir zufällig noch eine andere Lösung ein?

[ronrsnfld]

When you split into rows, it should be duplicating the ID, not "creating new ones".  Perhaps you can show more information about exactly how your original data is set up, or upload a sample file to some sharing site (eg OneDrive) and post a link here.

[Anonymous]

Sure, that is probably the easiest. The link to the PowerBI file is here. 

It did now work to split the columns in rows (apparently I did something wrong). But anyhow there are still no vizualizations in the map. As seen in the screenshot. 

Thank you a lot!

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Bing and Filled maps are not shown up in visualization pane has been confirmed as a known issue internally. 

And the issue should have been resolved by now. Please download the latest version of Power BI Desktop and try it.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-stephen-msft]

Hi @Anonymous ,

I downloaded your file and it works on my side.

Maybe you should try to change the data category to country and try again.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.