number of skyscrapers for each country

Question

I am a beginner in Power BI. I am trying to create a column with number of skyscrapers for each country but dont know how to do it.

Position    Building Name                    Meters Floors  Completed   City    Country
1           Burj Khalifa                     828    163 2010    Dubai   AE
2           Shanghai Tower                   632    128 2015    Shanghai    CN
3           Makkah Royal Clock Tower         601    120 2012    Mecca   SA
4           Ping An Finance Center           599.1  115 2017    Shenzhen    CN
5           Lotte World Tower                554.5  123 2017    Seoul   KR
6           One World Trade Center           541.3  94  2014    New York City   US
7           Guangzhou CTF Finance Centre     530    111 2016    Guangzhou   CN
8           TAIPEI 101                       508    101 2004    Taipei  TW
9           Shanghai World Financial Center  492    101 2008    Shanghai    CN
10          International Commerce Centre    484    108 2010    Hong Kong   CN
11          Changsha IFS Tower T1            452.1  94  2018    Changsha    CN
12          Petronas Twin Tower 1            451.9  88  1998    Kuala Lumpur    MY
12          Petronas Twin Tower 2            451.9  88  1998    Kuala Lumpur    MY
14          Zifeng Tower                     450    66  2010    Nanjing CN
15          Willis Tower                     442.1  108 1974    Chicago US
16          KK100                            441.8  100 2011    Shenzhen    CN
17          Guangzhou International Finance Center  438.6   103 2010    Guangzhou   CN
18          432 Park Avenue                  425.7  85  2015    New York City   US
19          Marina 101                       425    101 2017    Dubai   AE

Thank you in advance.

Answer 1

If you want to do it in M it is easy. Just Group it by Country:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZTdb9owEMD/FYvnKooTJ00es6xsKIWhkgox1AePusQl2FUgQ+1fv7szlIRpPAyJ80d8P9+nl8sBH9wMvrTNKysqWesXCcskSEDyOAQZ+NyH4Wv7S2rGWHYHCxBPN8tBANNZJc26gk+lPagGNuIQtzkRQDfqnoFfPoENEKiP+LHcbGTFHuy7rFle29XmTPI5kXxHQuxYrVYSKLMMsRlRBEyn2qxZZthQG2lWiuXK7IkRpalHFDIEKLdkjzIf8L+0B4/c2/1esblt6udPQ6JIeBGZEnYgyrY1AIoHWIBAQAzTH+ZTvZHPXVME91A/FQ6Cw0Qd2MI2G5br/TvAHmewCQJheMm3FiL3UdmW5eWw512jEBn65Bt3xLin0fcN01Fmo+ndiHGKa+RTjn2n66M1pdRvCrJUznExJ720mz/nlzNDY75Ovok06MKSK0mnahqhnpF7bQ1i7Harmq5jIhHES84F+N1CigsUFzy8M8fLdpVko+HMpY2VuC+igNJ/innSPXsJQhemat+ATTtWHrQ5khyIexiKhIKWpjgUrawlu2+3b22DsPECK3RxHRb8Dwyt/6lfFDh/qkkRYVDi+BygiTSv2AY9n7Bs57qu9e6sKVxMXHR5eisoKHol17ZXgFRPRcF9n7S45wrm2I78SiPxfu32s32sYmSGjth5Zv5VvpQ5EQZsKqFbst/KtEQIIg/vSqLza3Otp3hKT04DJhzbAAidyiXD/3rqnv4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Position = _t, #"Building Name" = _t, Meters = _t, Floors = _t, Completed = _t, City = _t, Country = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position", Int64.Type}, {"Building Name", type text}, {"Meters", type number}, {"Floors", Int64.Type}, {"Completed", Int64.Type}, {"City", type text}, {"Country", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"