Forum Discussion
Help with local maximums
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...
Hi Anonymous
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 Anonymous
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. |