Forum Discussion
How can I consolidate rows?
Hello I have that data: ID Header1 Header2 ID1 4 9 ID1 7 4 ID2 2 8 ID2 6 3 How can I consolidate the rows by ID and take the maximum of column2 and the maximum of...
Hi Anonymous ,
If you wnat to redo your table you need to use the Query Editor and group by ID and then MAX for both columns:
check the code for the query editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMgFiS6VYHRjfHCwG4RsB2SBsgcQ3A2JjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Header1 = _t, Header2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Header1", Int64.Type}, {"Header2", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Header1", each List.Max([Header1]), type number}, {"Header2", each List.Max([Header2]), type number}}) in #"Grouped Rows"If want on the visualization you need to make a table visualization and then select the maximum values for each of the columns.
Hi Anonymous
I've completed this in Power Query. Paste the following code into a blank query using Advanced Editor to follow my steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMgFiS6VYHRjfHCwG4RsB2SBsgcQ3A2JjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Header1 = _t, Header2 = _t]),
groupID = Table.Group(Source, {"ID"}, {{"data", each _, type table [ID=text, Header1=text, Header2=text]}}),
addMaxHeader1 = Table.AddColumn(groupID, "maxHeader1", each Table.Max([data], "Header1")),
addMaxHeader2 = Table.AddColumn(addMaxHeader1, "maxHeader2", each Table.Max([data], "Header2")),
expandMaxHeader1 = Table.ExpandRecordColumn(addMaxHeader2, "maxHeader1", {"Header1"}, {"Header1"}),
expandMaxHeader2 = Table.ExpandRecordColumn(expandMaxHeader1, "maxHeader2", {"Header2"}, {"Header2"}),
remDataCol = Table.RemoveColumns(expandMaxHeader2,{"data"}),
chgAllTypes = Table.TransformColumnTypes(remDataCol,{{"Header1", Int64.Type}, {"Header2", Int64.Type}})
in
chgAllTypes
I get the following output:
Pete