Forum Discussion
Solved
Merge 2 Row by date and name column
Hi Guru out there, Some help and guidance need, I have a table shown below (on the left side) and I would like to transform the data (which result on the right side). Thanks you in advance f...
Click the NameList and DateTempTaken and then on the Home ribbon, click Group By, then on the Aggregations, type in the new AM Column name and PM Column name (can be the same as before), and select MAX() for both aggregations, then OK.
Hi ulalah,
Group by Name and date column in your tabel.
M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi1OLTJU0lEy0jfUNzIwMgAygcjYTM9IKVYHqzRQzgSkCiZthE23MS5pdN3GmLrNcUkC9Zoi64U4zAjVZkNc0samepaY7kbTbYJLGqjbDNPdRrh8jSYNDk+Q7lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Name list" = _t, #"date temp taken" = _t, #"AM Reading" = _t, #"PM Reading" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name list", type text}, {"date temp taken", type date}, {"AM Reading", type number}, {"PM Reading", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name list", "date temp taken"}, {{"AM", each List.Sum([AM Reading]), type number}, {"PM", each List.Sum([PM Reading]), type number}})
in
#"Grouped Rows"