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 identical in structure and contains the same ID however not all rows for the ID contain the same same data in their columns

 

In the below example, I have 4 source rows for INC00123. I am looking to search each row and if the data for the columns already exists in my target record, ignore the record.  Where the data in the column is updated or new - I am looking to update the target row .  Final result should be 1 output row - as per below.

 

Source

RowIDIDSales PersonSalesAreaCode
1INC00123Harry30 
2INC00123Harry30 
3INC00123Harry30 
4INC00123  1255

 

 

Target

IDSales PersonSalesAreaCode
INC00123Harry301255

 

Any help would be greatly appreciated.

 

thanks

 

Need Help

5 Replies

  • 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.

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi mgib,

    You could refer to below M code to see whether it work or not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfL0czYwMDQyBjI9EouKKoG0sQGQUFCK1YlWMiKowpigChNUFWBxHSVDI1NTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RowID = _t, ID = _t, #"Sales Person" = _t, Sales = _t, AreaCode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RowID", Int64.Type}, {"ID", type text}, {"Sales Person", type text}, {"Sales", Int64.Type}, {"AreaCode", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Sales Person"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Sales Person", "Sales"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"AreaCode"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"ID", "Sales Person"}, {{"max", each List.Max([Sales]), type number}, {"maxa", each List.Max([AreaCode]), type number}})
in #"Grouped Rows"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.