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
Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Why is there no Sales Person entry for RowID 4?  Will area code always be a numeric column?

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.