Forum Discussion

ulalah's avatar
ulalah
Regular Visitor
6 years ago
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 for your help and guidance.

 

Pat.

Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

     

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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.

     

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    Click the AM Reading column, then choose the Fill up button.

    Similarly, click the PM Reading column, then choose the Fill down button.

    Fill up an Down context menu in the Transform menu

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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"

     

  • ulalah's avatar
    ulalah
    Regular Visitor
    6 years ago

    Hi edhans v-frfei-msft artemus , I would like to thanks you for your guidance and taking time to look into my problem and provide the solution which I have mark the solution.

     

    You all have show me something that I didn't know and I have learn a new things today.

     

    Once again thanks you for your time.