How to pivot this data...?

[arjuna]

I have two tables I want to create a simple relationship between (year and number).

One is just a Calendar() of each year 2015 through 2025:

Date,Year

4/29/2015,2015

4/30/2015,2015

I have a SQL summary table that looks like this:

2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025 <-- column names

1,0.5,2.5,3,4.5,-1,2,3.5,4,2,1,4 <-- row

I basically want to create a pivot'd relationship in power bi, but I'm not sure how without creating a huge mess of things. What is the minimum number of steps to accomplish this kind of link? "2015" Year in the calendar table should associate with "1" in my summary table.

[amitchandak · Accepted solution]

@arjuna , You should unpivot this table

https://radacad.com/pivot-and-unpivot-with-power-bi

After that, if you only have a year, then create date like

Date = Date([Year],1,1)

Join this with date of calendar

[mahoneypat]

You definitely should unpivot your summary table so you have a Year column that can have a relationship with your date table.  It will make your analysis and visualization much simpler/easier.

Regards,

Pat