cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lauraabout_
New Member

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

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)

                                  Bildschirmfoto 2021-12-05 um 21.37.49.png

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). 

 

Bildschirmfoto 2021-12-05 um 21.32.18.png

 


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

1 ACCEPTED SOLUTION

Hi @Lauraabout_ ,

 

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.

 

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
Super User

You can split into rows instead of columns.

AlexisOlson_0-1638750457465.png

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

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?

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.

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. 

 

Anmerkung 2021-12-06 193902-2.png

 

Thank you a lot!

Hi @Lauraabout_ ,

 

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.

 

Hi @Lauraabout_ ,

 

I downloaded your file and it works on my side.

vstephenmsft_0-1639030127416.png

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

vstephenmsft_1-1639030174603.png

 

 

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.

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!