I'm just starting to learn Power BI and I'm facing an issue which I think is easy to solve.
I have 3 tables linked by the column CCA3. The third table contains a column for some years showing the number of poeple for each country at this date.
I want to make a dashboard where I may show the evolution of the population of the different countries over the time, without loosing the link with the other tables. So I cannot transpose the table to get the years in a column because in this case the column CCA3 disapear and the link is broken.
Original dataset is available on the following link : https://www.kaggle.com/datasets/iamsouravbanerjee/world-population-dataset/download?datasetVersionNumber=3
The M code generated by Power Query is the following :
let
Source = Csv.Document(File.Contents("C:\Users\benny\Desktop\world_population.csv"),[Delimiter=",", Columns=17, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Rank", Int64.Type}, {"CCA3", type text}, {"Country", type text}, {"Capital", type text}, {"Continent", type text}, {"2022 Population", Int64.Type}, {"2020 Population", Int64.Type}, {"2015 Population", Int64.Type}, {"2010 Population", Int64.Type}, {"2000 Population", Int64.Type}, {"1990 Population", Int64.Type}, {"1980 Population", Int64.Type}, {"1970 Population", Int64.Type}, {"Area (km²)", Int64.Type}, {"Density (per km²)", type text}, {"Growth Rate", type text}, {"World Population Percentage", type text}}),
#"Lignes triées" = Table.Sort(#"Type modifié",{{"Rank", Order.Ascending}}),
#"Valeur remplacée" = Table.ReplaceValue(#"Lignes triées",".",",",Replacer.ReplaceText,{"Density (per km²)"}),
#"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée",".",",",Replacer.ReplaceText,{"Growth Rate"}),
#"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée1",{{"Growth Rate", type number}, {"Density (per km²)", type number}}),
#"Valeur remplacée2" = Table.ReplaceValue(#"Type modifié1",".",",",Replacer.ReplaceText,{"World Population Percentage"}),
#"Type modifié2" = Table.TransformColumnTypes(#"Valeur remplacée2",{{"World Population Percentage", type number}}),
#"Soustrait de la colonne" = Table.TransformColumns(#"Type modifié2", {{"Growth Rate", each _ - 1, type number}}),
#"Autres colonnes supprimées" = Table.SelectColumns(#"Soustrait de la colonne",{"Rank", "CCA3", "2022 Population", "2020 Population", "2015 Population", "2010 Population", "2000 Population", "1990 Population", "1980 Population", "1970 Population", "Area (km²)", "Density (per km²)", "Growth Rate", "World Population Percentage"}),
#"Colonnes permutées" = Table.ReorderColumns(#"Autres colonnes supprimées",{"CCA3", "Rank", "2022 Population", "2020 Population", "2015 Population", "2010 Population", "2000 Population", "1990 Population", "1980 Population", "1970 Population", "Area (km²)", "Density (per km²)", "Growth Rate", "World Population Percentage"}),
#"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes permutées",{"CCA3", "Area (km²)", "Density (per km²)", "Growth Rate", "World Population Percentage"}),
#"Duplication de la colonne" = Table.DuplicateColumn(#"Autres colonnes supprimées1", "World Population Percentage", "World Population Percentage - Copier"),
#"Cube calculé" = Table.TransformColumns(#"Duplication de la colonne",{{"World Population Percentage - Copier", each Number.Power(_, 3), type number}}),
#"Colonnes renommées" = Table.RenameColumns(#"Cube calculé",{{"World Population Percentage - Copier", "WPP Size"}}),
#"Duplication de la colonne1" = Table.DuplicateColumn(#"Colonnes renommées", "WPP Size", "WPP Size - Copier"),
#"Colonnes supprimées" = Table.RemoveColumns(#"Duplication de la colonne1",{"WPP Size - Copier", "WPP Size"}),
#"Type modifié3" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"World Population Percentage", Percentage.Type}, {"Growth Rate", Percentage.Type}}),
#"Colonne divisée" = Table.TransformColumns(#"Type modifié3", {{"World Population Percentage", each _ / 100, Percentage.Type}})
in #"Colonne divisée"
Is there any way to do it with Power BI ?
Thanks a lot.
Cheers :)
