Error in unpivot DAX query

[Vish_korg]

I have created table "H1_daily" where I have the following data

Equipment failure loss	Setup & adjustment loss
20	10
30	5
15	8
10	6

I want the output as follows

Total losses	Down time
Equipment failure loss	20
Setup & adjustment loss	10
Equipment failure loss	30
Setup & adjustment loss	5
Equipment failure loss	15
Setup & adjustment loss	8
Equipment failure loss	10
Setup & adjustment loss	6

I have used the query

Down_time = union(

          SELECTCOLUMNS('H1_daily',"Total losses","Equipment failure loss",'H1_daily'[Equipment failure loss]),

          SELECTCOLUMNS('H1_daily',"Total Losses","Setup & adjustment loss",'H1_daily'[Setup & Adjustment loss]))

getting the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Can any one help why this error is comming and what is the solution?Though I have got the solution in unpivot query 

directly I want to know what is wrong in my above query?

[jgeddes · Accepted solution]

It looks like you are trying to create a table using a measure. 
Use this code to create a new table and then you can create the visual you are looking for.

DowntimeTable = 
UNION(
        SELECTCOLUMNS('H1_daily',"Total losses","Equipment failure loss", "Down time", 'H1_daily'[Equipment failure loss]),
        SELECTCOLUMNS('H1_daily',"Total Losses","Setup & adjustment loss","Down time", 'H1_daily'[Setup & Adjustment loss])
    )

[slorin]

Hi @Cristian_Angyal 

You can use UnpivotOtherColumns without "Custom" column

...
AddCustomColumn = Table.AddColumn(SourceTable, "Custom", each 1),
UnpivotedData = Table.UnpivotOtherColumns(AddCustomColumn, {"Custom"}, "Total Losses", "Down Time"),
ChangedType = Table.TransformColumnTypes(UnpivotedData, {{"Down Time", Int64.Type}}),
RemoveCustomColumn = Table.RemoveColumns(ChangedType, {"Custom"})
...

let
Source = YourSource
UnpivotedData = Table.UnpivotOtherColumns(Source, {}, "Total Losses", "Down Time"),
ChangedType = Table.TransformColumnTypes(UnpivotedData, {{"Down Time", Int64.Type}})
in
ChangedType

Stéphane

[Cristian_Angyal]

Thanks @slorin .

You are right and adding and empy list works well ... but I skipped already a step, renaming the columns directly from UNPIVOT.
I wanted to show exactly what is happening, as I'm thinking of myself some years ago when M Language understanding was not as it is these days 😉.

Cristian

[Cristian_Angyal · Accepted solution]

Hey @Vish_korg ,

If you are trying to build a DAX Table then the solution provided by @jgeddes  should be ok.

On the other hand, if you aleready have the initial table in Power Query, why not UNPIVOT it there and simplify your DAX code?!?

Here is the M code to do it in PQ:

// This query processes a table containing equipment failure and setup adjustment loss data, 
// adds a custom column, unpivots the data, changes the data type of the downtime column, and removes an extra column.
// Developed by Cristian Angyal with Custom Chat GPT "Power Query Wizard"

let
    // Create initial table from compressed JSON data
    SourceTable = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WMjJQ0lEyNFCK1YlWMgaxTcFMQ1Mg0wLCBImaKcXGAgA=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Equipment failure loss" = _t, #"Setup & adjustment loss" = _t]
    ),

    // Add a custom column with a static value of 1 --- could be ANY value
    AddCustomColumn = Table.AddColumn(SourceTable, "Custom", each 1),

    // Unpivot all columns except the "Custom" column
    UnpivotedData = Table.UnpivotOtherColumns(AddCustomColumn, {"Custom"}, "Total Losses", "Down Time"),

    // Change the data type of the "DownTime" column to Int64
    ChangedType = Table.TransformColumnTypes(UnpivotedData, {{"Down Time", Int64.Type}}),

    // Remove the "Custom" column
    RemoveCustomColumn = Table.RemoveColumns(ChangedType, {"Custom"})

in
    // Output the final table
    RemoveCustomColumn

Hope it helps.

If this answer was helpful, please consider accepting it as the solution to help the other members find it more quickly.

Kudos appreciated also 😉

Cheers,

Cristian Angyal
LinkedIn  |  X (Twitter) |   Romania Power BI User Group  |  YouTube

 

[jgeddes · Accepted solution]

It looks like you are trying to create a table using a measure. 
Use this code to create a new table and then you can create the visual you are looking for.

DowntimeTable = 
UNION(
        SELECTCOLUMNS('H1_daily',"Total losses","Equipment failure loss", "Down time", 'H1_daily'[Equipment failure loss]),
        SELECTCOLUMNS('H1_daily',"Total Losses","Setup & adjustment loss","Down time", 'H1_daily'[Setup & Adjustment loss])
    )