Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Proud to be a Datanaut!
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.
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
Proud to be a Datanaut!
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
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
Proud to be a Datanaut!