Forum Discussion
Evolution Graph
I am trying to create a graph of population evolution for different cities, but I don't know how to create it the way I have structured the data.
I have two tables a first table (table1) with the cities and the information of the different apartments they have for sale (there is more than one apartment for each city).
On the other hand, (table2), my table of population evolution by city, in which my first column is the name of the city and then I have a column for each year of study, in total I have 10 years. This table has 11 columns.
I have created a relationship of several to 1 between table1 and table2.
This relationship must be maintained, because from table1 I have to be able to filter information from table2.
My goal is to create the evolution graph from table 2, showing each year and the population of this year.
This is an image of table1
This is an image of table 2
And this is an image of the model
My goal is to create something like this
Thanks in advance
- Anonymous4 years ago
Hi schmid23 ,
I created two tables for testing.
Table1.
Table2.
Relationships.
If you want to minimize the impact on the original model. You can make a copy of Table1 in PQ and rename it as Table3. Then select the table3 [Municipio] column, right click and click to unpivot other columns. Closed and
apply.
Relationships.
Please create a new line chart.
The PBIX file is attached for reference.
Best Regards,
Community Support Team_GaoIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
2 Replies
- KerKolPower Participant
You'll need to unpivot your data for Table 2 in Power Query
So that you can then drag the dates field into the Axis
How to Unpivot in Excel with Power Query + Video Tutorial (excelcampus.com)
Unpivot And Pivot Basics In Power BI - Query Editor Review - Enterprise DNA
will then help to have fact and dimension tables, with a one-to-many relationship from dimension table to fact table:
- AnonymousNot applicable
Hi schmid23 ,
I created two tables for testing.
Table1.
Table2.
Relationships.
If you want to minimize the impact on the original model. You can make a copy of Table1 in PQ and rename it as Table3. Then select the table3 [Municipio] column, right click and click to unpivot other columns. Closed and
apply.
Relationships.
Please create a new line chart.
The PBIX file is attached for reference.
Best Regards,
Community Support Team_GaoIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data