Unpivot columns using dax

[Jonathan47]

Hi, I've created a new table using the create new table feature and pulled values from another table to create an array of columns. My problem is I cant manipulate this in power query editor since it's not a query. I'd like to unpivote all of these columns and create an atribute, value and index column, how can I go about this?

The code looks like this:

Normalized =

(SELECTCOLUMNS(

'DC Power',

"Date", 'DC Power'[Date],

"Inverter 01.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 01.1"),

"Inverter 01.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 01.2"),

"Inverter 02.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 02.1"),

"Inverter 02.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 02.2"),

"Inverter 03.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 03.1"),

"Inverter 03.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 03.2"),

"Inverter 04.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 04.1"),

"Inverter 04.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 04.2"),

"Inverter 05.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 05.1"),

"Inverter 05.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 05.2"),

"Inverter 06.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 06.1"),

"Inverter 06.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 06.2"),

"Inverter 07.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 07.1"),

"Inverter 07.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 07.2"),

"Inverter 08.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 08.1"),

"Inverter 08.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 08.2"),

"Inverter 09.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 09.1"),

"Inverter 09.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 09.2"),

"Inverter 10.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 10.1"),

"Inverter 10.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 10.2"),

"Inverter 11.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 11.1"),

"Inverter 11.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 11.2"),

"Inverter 12.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 12.1"),

"Inverter 12.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 12.2"),

"Inverter 13.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 13.1"),

"Inverter 13.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 13.2"),

"Inverter 14.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 14.1"),

"Inverter 14.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 14.2"),

"Inverter 15.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 15.1"),

"Inverter 15.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 15.2"),

"Inverter 16.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 16.1"),

"Inverter 16.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 16.2"),

"Inverter 17.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 17.1"),

"Inverter 17.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 17.2"),

"Inverter 18.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 18.1"),

"Inverter 18.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 18.2"),

"Inverter 19.1", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 19.1"),

"Inverter 19.2", 'DC Power'[Karadoc Main KSF Inverter 01.1DC Total Power [kW]]]*(1-(0.0041*(25-'DC Power'[Module Temp.])))/CALCULATE(values(Info[Installed DC Power [kWp]]]),Info[Name] = "Inverter 19.2")

))

[v-diye-msft · Accepted solution]

Hi @Jonathan47 

I believe you can refer to the UNION function:

https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax

Here're similar posts for your reference:

https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832 

[lbendlin]

"My problem is I cant manipulate this in power query editor since it's not a query. "

Unless this is a live connection or Direct Query I would challenge your statement.  Surely the data sources for your DAX table come from a query somewhere?

[Jonathan47]

The data used in the calculations for my dax table was originally sourced from an excel model, so I dont think it is a direct query or live connection. However, my understanding is limited so not sure how to go about this other than using UNION (very tedious when unpivoting alot of columns)