Forum Discussion

PBI_Curve's avatar
PBI_Curve
Icon for Helper I rankHelper I
8 years ago
Solved

Converting Excel Table with Dates to PBI Suited Format

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 19...
Need Help
Tips and Tricks
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
8 years ago

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's avatar
PBI_Curve
Icon for Helper I rankHelper I
8 years ago

Thanks a million v-qiuyu-msft.

 

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