How to deal with columns with years as name in power query to use it as time serie

Viewed 18

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. enter image description here 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 :)

1 Answers

If the year (number) is a column name it is called a "pivoted table" (Excel Style) which brings you nowhere in Power BI.

But in Power Query you can easily transform - unpivot all the YYYY Population columns and you receive 2 new columns "Attribute" containing the years and "Value" containing the population numbers.

enter image description here

Now you simply have to extract the year numbers from the "Attribute" column

Related