Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I would need some help with Power Query.
The point is that I have an Excel file with the data from 2 machines. I have joined both tables in a new one to have the data of both machines with different or simmilar NumOP (Operation number).
As you´ll see in the PBI attached, the column structure is: TIME, COMPA (component A), COMPB (component B), NumOP, IDmachine.
I would like to take the maximum values of COMPA and COMPB (same line) according to the Operation number (NumOP).
The main problem is that a machine could shutdown and restart in 0 position (see the example), so the program should be able to give us an output like this:
So the final result would be:
Could you please help me on this topic??
Thank you so much for your support.
In this link you will find the Excel file and the PBI file
Solved! Go to Solution.
Hi @DBB99
Place the following M code in a blank query to see the steps of a possible solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY67DcAgDAVXiVwj4Q82kFWQ918jISJVUuDiJBf3rBsDWDJxZmQ8SE7skIBeClnreF+Cisbg6dfnxa6Pi11fgj0l2KPB/9H+MoNUI0VTNgssHrnW76KB+wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, COMPA = _t, COMPB = _t, NumOP = _t, MachineId = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"COMPB", type number}, {"COMPA", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"MachineId"}, {{"GroupCol", each let s1_ = Table.AddIndexColumn(_,"Index", 1,1), s2_ = Table.Sort(s1_,{{"Index", Order.Ascending}}),
list_ = List.Transform(List.Zip({s2_[COMPA], s2_[COMPB]}), each _{0}+_{1}),
s3_ = Table.AddColumn(s2_, "Custom2", each List.Count(List.PositionOf(List.Range(list_,0,[Index]), 0, Occurrence.All)), Int64.Type) in s3_ }}),
#"Expanded GroupCol" = Table.ExpandTableColumn(#"Grouped Rows", "GroupCol", {"Time", "COMPA", "COMPB", "NumOP", "Index", "Custom2"}, {"Time", "COMPA", "COMPB", "NumOP", "Index", "Custom2"}),
#"Grouped Rows1" = Table.Group(#"Expanded GroupCol", {"MachineId", "Custom2"}, {{"COMPA", each List.Max([COMPA])}, {"COMPB", each List.Max([COMPB])}, {"NumOP", each [NumOP]{0}}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"Custom2"})
in
#"Removed Columns"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi @DBB99
Place the following M code in a blank query to see the steps of a possible solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY67DcAgDAVXiVwj4Q82kFWQ918jISJVUuDiJBf3rBsDWDJxZmQ8SE7skIBeClnreF+Cisbg6dfnxa6Pi11fgj0l2KPB/9H+MoNUI0VTNgssHrnW76KB+wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, COMPA = _t, COMPB = _t, NumOP = _t, MachineId = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"COMPB", type number}, {"COMPA", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"MachineId"}, {{"GroupCol", each let s1_ = Table.AddIndexColumn(_,"Index", 1,1), s2_ = Table.Sort(s1_,{{"Index", Order.Ascending}}),
list_ = List.Transform(List.Zip({s2_[COMPA], s2_[COMPB]}), each _{0}+_{1}),
s3_ = Table.AddColumn(s2_, "Custom2", each List.Count(List.PositionOf(List.Range(list_,0,[Index]), 0, Occurrence.All)), Int64.Type) in s3_ }}),
#"Expanded GroupCol" = Table.ExpandTableColumn(#"Grouped Rows", "GroupCol", {"Time", "COMPA", "COMPB", "NumOP", "Index", "Custom2"}, {"Time", "COMPA", "COMPB", "NumOP", "Index", "Custom2"}),
#"Grouped Rows1" = Table.Group(#"Expanded GroupCol", {"MachineId", "Custom2"}, {{"COMPA", each List.Max([COMPA])}, {"COMPB", each List.Max([COMPB])}, {"NumOP", each [NumOP]{0}}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"Custom2"})
in
#"Removed Columns"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi @DBB99
Place the following M code in a blank query to see the steps of a possible solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY67DcAgDAVXiVwj4Q82kFWQ918jISJVUuDiJBf3rBsDWDJxZmQ8SE7skIBeClnreF+Cisbg6dfnxa6Pi11fgj0l2KPB/9H+MoNUI0VTNgssHrnW76KB+wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, COMPA = _t, COMPB = _t, NumOP = _t, MachineId = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"COMPB", type number}, {"COMPA", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"MachineId"}, {{"GroupCol", each let s1_ = Table.AddIndexColumn(_,"Index", 1,1), s2_ = Table.Sort(s1_,{{"Index", Order.Ascending}}),
list_ = List.Transform(List.Zip({s2_[COMPA], s2_[COMPB]}), each _{0}+_{1}),
s3_ = Table.AddColumn(s2_, "Custom2", each List.Count(List.PositionOf(List.Range(list_,0,[Index]), 0, Occurrence.All)), Int64.Type) in s3_ }}),
#"Expanded GroupCol" = Table.ExpandTableColumn(#"Grouped Rows", "GroupCol", {"Time", "COMPA", "COMPB", "NumOP", "Index", "Custom2"}, {"Time", "COMPA", "COMPB", "NumOP", "Index", "Custom2"}),
#"Grouped Rows1" = Table.Group(#"Expanded GroupCol", {"MachineId", "Custom2"}, {{"COMPA", each List.Max([COMPA])}, {"COMPB", each List.Max([COMPB])}, {"NumOP", each [NumOP]{0}}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"Custom2"})
in
#"Removed Columns"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi @DBB99
Place the following M code in a blank query to see the steps of a possible solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY67DcAgDAVXiVwj4Q82kFWQ918jISJVUuDiJBf3rBsDWDJxZmQ8SE7skIBeClnreF+Cisbg6dfnxa6Pi11fgj0l2KPB/9H+MoNUI0VTNgssHrnW76KB+wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, COMPA = _t, COMPB = _t, NumOP = _t, MachineId = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"COMPB", type number}, {"COMPA", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"MachineId"}, {{"GroupCol", each let s1_ = Table.AddIndexColumn(_,"Index", 1,1), s2_ = Table.Sort(s1_,{{"Index", Order.Ascending}}),
list_ = List.Transform(List.Zip({s2_[COMPA], s2_[COMPB]}), each _{0}+_{1}),
s3_ = Table.AddColumn(s2_, "Custom2", each List.Count(List.PositionOf(List.Range(list_,0,[Index]), 0, Occurrence.All)), Int64.Type) in s3_ }}),
#"Expanded GroupCol" = Table.ExpandTableColumn(#"Grouped Rows", "GroupCol", {"Time", "COMPA", "COMPB", "NumOP", "Index", "Custom2"}, {"Time", "COMPA", "COMPB", "NumOP", "Index", "Custom2"}),
#"Grouped Rows1" = Table.Group(#"Expanded GroupCol", {"MachineId", "Custom2"}, {{"COMPA", each List.Max([COMPA])}, {"COMPB", each List.Max([COMPB])}, {"NumOP", each [NumOP]{0}}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"Custom2"})
in
#"Removed Columns"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.