Converting Excel Table with Dates to PBI Suited Format

[PBI_Curve]

I often have tables in csv files similar to the below. In this example, it's the average aid spend per head of population per year between 1960 and 1970.

Country	1960	1961	1962	1963	1964	1965	1966	1967	1968	1969	1970
Australia	€  51.66	€  60.15	€  61.22	€  75.61	€  75.32	€  85.28	€  89.90	€104.07	€  98.08	€107.00	€113.54
Austria	€    0.19	€    5.24	€    3.97	€    6.95	€  10.53	€  16.33	€  18.10	€  20.85	€  21.91	€  18.46	€  12.85
Belgium	€111.09	€  99.92	€  74.03	€  81.59	€  68.91	€  92.32	€  66.19	€  74.21	€  71.75	€  91.05	€  88.75
Canada	€  24.62	€  23.42	€  16.45	€  24.77	€  28.49	€  33.43	€  62.37	€  59.29	€  55.84	€  58.18	€  83.36
Denmark	€  16.26	€  23.71	€  20.39	€  22.18	€  24.50	€  29.17	€  43.84	€  51.88	€  57.93	€102.28	€101.14
France	€175.74	€184.58	€181.09	€145.09	€139.31	€122.03	€116.77	€124.61	€123.46	€135.04	€103.67
Germany	€  47.05	€  70.19	€  73.50	€  67.60	€  76.42	€  72.75	€  64.17	€  76.24	€  81.40	€  79.17	€  69.93
Italy	€  20.13	€  15.19	€  19.13	€  15.38	€    9.73	€  11.68	€  14.81	€  28.17	€  25.93	€  22.11	€  23.32
Japan	€  14.50	€  13.82	€  10.36	€  15.76	€  12.42	€  24.28	€  27.09	€  34.26	€  29.46	€  33.70	€  32.49
Netherlands	€  45.18	€  66.40	€  72.98	€  39.96	€  47.23	€  62.19	€  78.69	€  90.04	€  93.56	€100.16	€127.62
Norway	€  20.40	€  25.80	€  26.27	€  34.23	€  31.57	€  35.26	€  41.66	€  41.31	€  71.87	€  76.11	€  83.41
Sweden	€    7.76	€    9.40	€  19.76	€  23.79	€  32.15	€  34.98	€  48.80	€  48.26	€  55.99	€  90.77	€  83.31
Switzerland	€  10.40	€  22.56	€  12.91	€  15.24	€  21.09	€  25.80	€  26.82	€  25.37	€  45.14	€  53.67	€  50.64
United Kingdom	€  92.84	€100.45	€  88.37	€  85.35	€  97.67	€  87.74	€  86.17	€  85.21	€  79.69	€  86.28	€  80.57
United States	€  86.99	€  92.77	€  98.79	€103.99	€101.62	€110.08	€100.46	€  83.18	€  91.96	€  76.32	€  66.91

Is there a quick and/or simple way to transform those types of layouts into the below format which makes analysing and creating visuals in PBI much easier?

Country	Spend p/p	Year
Australia	€        51.66	1960
Australia	€        60.15	1961
Australia	€        61.22	1962
Australia	€        75.61	1963
Australia	€        75.32	1964
Australia	€        85.28	1965
Australia	€        89.90	1966
Australia	€      104.07	1967
Australia	€        98.08	1968
Australia	€      107.00	1969
Australia	€      113.54	1970
Austria	€         0.19	1960
Austria	€         5.24	1961
Austria	€         3.97	1962
Austria	€         6.95	1963
Austria	€        10.53	1964
Austria	€        16.33	1965
Austria	€        18.10	1966
Austria	€        20.85	1967
Austria	€        21.91	1968
Austria	€        18.46	1969
Austria	€        12.85	1970
and so on….	……	…….

All help/suggestions appreciated.

Cheers.

[v-qiuyu-msft · Accepted solution]

Hi @PBI_Curve,

Based on my test, you can refer to below steps in Query Editor:

1.I have entered some of the data you have post in Power BI desktop to do a test. We can select the [1960] and [1961] column (please select columns from 1960 to 1970 on your side) and use the Unpivot Columns function.

2. Reorder the [Value] column before the [Attribute] column and rename them.

For more information, see attached pbix file. 

Best Regards,
Qiuyun Yu

[PBI_Curve]

Thanks a million @v-qiuyu-msft.

I really need to put some time into learning pivot tables, power pivot etc.