Dax new table with multiple columns

[shei7141]

Hi, 

I have a table which has

Items, Yr2001, Yr2002, Yr2003....Yr2046

How can I create a new table which can return the following

Items (<<only unique values), Yrs, Value

x, 2001, 5

x, 2002,15

.

.

y,2046,1

y, 2001, 5

y, 2002,15

.

.

y,2046,1

Thanks in advance

A

[Fowmy]

@shei7141 

What is 5 and 15  ?
What x 5 and y  ?

Please elaborate? 

x, 2001, 5

x, 2002,15

[shei7141]

thanks mate, thats value under each year (i.e. field in the original)

for example

Item, Yr2001,Yr2002,Yr2006

x,5,15,1

[Fowmy]

@shei7141 
 
Sorry not clear what you are trying to get, please provide some source data and give more clarity 

Thanks

[shei7141]

sorry for confusing the situation. I hope the following data sample will provide some clarity

Original > data
Item	Year2001	Year2002	Year2003
x	15	10	5
y	1	15	20
z	15	30	40

Target
Item	Year	Value
x	2001	15
y	2002	10
z	2003	5
x	2001	1
y	2002	15
z	2003	20
x	2001	15
y	2002	30
z	2003	40

[Fowmy]

@shei7141 

You can do in Power Query using UnPivot.

Select Item Column and right-click, select UnPivot Other Columns
Select Year Column and select Replace Value, to enter Year and blank.
Done

Watch This video: https://www.youtube.com/watch?v=Vff2kRBM95o

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[shei7141]

thanks heaps mate

My table is a calculated table and I am unable to accesss the edit query interface,

[Anonymous · Accepted solution]

I don't know how to programatically find out how many columns are there in your "calculated table", but if it is a fixed number of columns and you want to convert the same to the "Target" format, You could try this although, it is a bad example, and I feel, if you post the source table, instead of the calculated table, we can try out something.

Target = 
VAR T1 = 
    ADDCOLUMNS(
        ALL('OriginalData'[Item],'OriginalData'[Year2001]),
        "Year",2001)
VAR T2 = 
    ADDCOLUMNS(
        ALL('OriginalData'[Item],'OriginalData'[Year2002]),
        "Year",2002)
VAR T3 = 
    ADDCOLUMNS(
        ALL('OriginalData'[Item],'OriginalData'[Year2003]),
        "Year",2003)
RETURN
UNION(T1,T2,T3)

[shei7141]

Thank you both for your time and assistance - really appreciate it 

you guys are legend 🙂

[Fowmy]

@shei7141 

Please share a sample PBI file with source or sample data.

Thanks