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

[Tan_LC]

Hi, 

In below table, I would like to merge the rows by the attribute of Hour.

When merging the rows, Value for Cure Press shall be created on its own instead of sum up with the Machine Value.

Kindly refer to the expected result table for more details.

Date	Size No	Machine	Cure Press	Cycle Time	Hour	Value
1/2/2023	A045	205			10	39
1/2/2023	A045	205	3012	13.2	10	9
1/2/2023	A045	205			11	30
1/2/2023	A045	205	3012	13.2	11	9

Expected Result:

Date	Size No	Machine	Cure Press	Cycle Time	Hour	Machine Value	Cure Press Value
1/2/2023	A045	205	3012	13.2	10	39	9
1/2/2023	A045	205	3012	13.2	11	30	9

Thank you.

Regards,

Tan LC

[Mahesh0016]

Hello Tan_LC,

>> First Step Fill up Blank Value.

>> Second Add INDEX Column Start with 0.

>> Add Custom Column with this conditions >> try #"Added Index" [Value] {[Index]+1} otherwise null.

>>Remove Alternative Row >>

>>Remove Index Column if you want other wise keepit.

Here is M code of all Step :

#"Changed Type" = 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}}),
#"Filled Up" = Table.FillUp(#"Changed Type",{"Cure Press", "Cycle Time"}),
#"Added Index" = Table.AddIndexColumn(#"Filled Up", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Previous Data", each try #"Added Index" [Value] {[Index]+1} otherwise                                         null),
#"Remove Rows" = Table.AlternateRows(#"Added Custom",1,1,1),
#"Removed Columns" = Table.RemoveColumns(#"Remove Rows",{"Index"})
in
    #"Removed Columns"

@Tan_LC 
if this post help you please mark as solutions and like.

[BA_Pete]

Hi @Tan_LC ,

There's two ways to go about this. One ends in your desired output structure, the other in the 'correct' data structure:

Both start by adding a new custom column, something like this:

if [Cure Press] = null then "Machine Value"
else "Cure Press Value"

At this point, your data is actually optimally structured for Power BI reporting, and I would recommend sending the table to the data model like this.

If you really need your data structure as per your example, you can now just pivot your new custom column and use [Value] as your values.

Pete

[Tan_LC]

Hi @BA_Pete ,

In your solution given, I couldn't work out how to turn "Value" column into "Machine Value" & "Cure Press Value" column.  

Regards,

Tan LC

[BA_Pete · Accepted solution]

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