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
Anonymous
Not applicable

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

 

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

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

Hi @Anonymous ,

 

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

Top Solution Authors
Top Kudoed Authors