Merge with a custom new column

[Anonymous]

I have a table with dates and production values as following: .

I want to make to merge the columns and make a new table with 3 columns: Date, Production and Country. Currently I managed to merge the date and production columns under two but am struggling to create a separate 3rd column where I could assign data from each column to a specific country. 

The code I have currently written:

newTable = VAR tbl1 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Algeria]) VAR tbl2 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Angola]) RETURN UNION (tbl1, tbl2)

Could it be possible to use a similar way to add a fixed value text string as country. A wrong solution I have tried:

newTable = VAR tbl1 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Algeria, "Country", Algeria]) VAR tbl2 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Angola], "Country", Angola]) RETURN UNION (tbl1, tbl2)

I understand that the problem is that I do not have currently columns names as such to add via SELECTCOLUMNS. Any possible solutions to this problem?

Best regards,

[parry2k · Accepted solution]

@Anonymous it should be like below, the best would be to do it in power query.

newTable = 
VAR tbl1 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Algeria],  "Country", "Algeria") 
VAR tbl2 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Angola], "Country", "Angola") 
RETURN UNION (tbl1, tbl2)

[Anonymous]

I used this code as a solution: 

newTable = VAR tbl1 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Algeria], "Country", "Algeria") VAR tbl2 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Angola], "Country", "Angola") VAR tbl3 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Ecuador], "Country", "Ecuador") VAR tbl4 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Gabon], "Country", "Gabon") VAR tbl5 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Iran], "Country", "Iran") VAR tbl6 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Iraq], "Country", "Iraq") VAR tbl7 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Kuwait], "Country", "Kuwait") VAR tbl8 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Libya], "Country", "Libya") VAR tbl9 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Nigeria], "Country", "Nigeria") VAR tbl10 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Qatar], "Country", "Qatar") VAR tbl11 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Saudi Arabia], "Country", "Saudi Arabia") VAR tbl12 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, United Arab Emirates], "Country", "United Arab Emirates") VAR tbl13 = SELECTCOLUMNS(Table2, "Date", [Month], "Production", [Crude Oil Production, Venezuela], "Country", "Venezuela") RETURN UNION(tbl1, tbl2, tbl3, tbl4, tbl5, tbl6, tbl7, tbl8, tbl9, tbl10, tbl11, tbl12, tbl13)

Unfortunately it makes a duplicate of every row. Is there a reason for it, and how could I fix this?

[parry2k]

@Anonymous as I mentioned it is much easier to do it in Power Query than using DAX

[Anonymous]

Thank you for your reply. It worked!