Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Consolidating source multiple rows into 1 row

Hi all, looking for some helpful hints to point me in the right direction for combining multiple incoming transactional rows for an ID into 1 output row to be used for reporting.   Each row is ide...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

In this scenario - the 4th source record shows a likely scenario of a delta change originating from the source transactional system where the ID has been updated.  In this record, ONLY the Area Code has been updated.

 

For this example, Area code will remain numeric.  

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Sales Person", type text}, {"Sales", Int64.Type}, {"AreaCode", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Sales Person"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"ID", "Sales Person"}, {{"Sale", each List.Min([Sales]), type number}, {"Area code", each List.Min([AreaCode]), type number}})
in
    #"Grouped Rows"

Hope this helps.