Merge different rows with the same attribute into one row but with different value column

 

To create your two columns, you just select the new custom column and go to the Transform tab > Pivot Column. In the dialog there, you select [Value] from the dropdown list to use as your new values for each column.

Paste this over the default code in Advanced Editor to see each step working:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMDJW0lFyNDAxBVJGBiBSAYoNDYCEsaVSrA5utcYGhkYgpcZ6RjAd+DXADTcE6ybFcEOI4bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Size No" = _t, Machine = _t, #"Cure Press" = _t, #"Cycle Time" = _t, Hour = _t, Value = _t]),
    chgType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Size No", type text}, {"Machine", Int64.Type}, {"Cure Press", Int64.Type}, {"Cycle Time", type number}, {"Hour", Int64.Type}, {"Value", Int64.Type}}),
    addValueType = Table.AddColumn(chgType, "ValueType", each if [Cure Press] = null then "Machine Value"
else "Cure Press Value"),
    pivotValueType = Table.Pivot(addValueType, List.Distinct(addValueType[ValueType]), "ValueType", "Value", List.Sum)
in
    pivotValueType

 

 

It will give you this output:

Once here, you can Group By [Date], [Size No], [Machine], [Hour], MAX([Cure Press]), MAX([Cycle Time]), MAX([Machine Value]), MAX([Cure Press Value]) to get a single row for each Date/Size/Machine/Hour instance.

 

However, as before, I would recommend not pivoting these into their own columns. It will likely cause you problems later if your model becomes larger and needs relationships and measures over multiple tables.

 

Pete