Consolidating source multiple rows into 1 row

[Anonymous]

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

RowID	ID	Sales Person	Sales	AreaCode
1	INC00123	Harry	30
2	INC00123	Harry	30
3	INC00123	Harry	30
4	INC00123			1255

Target

ID	Sales Person	Sales	AreaCode
INC00123	Harry	30	1255

Any help would be greatly appreciated.

thanks

[dax]

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.

[Ashish_Mathur]

Hi,

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

[Anonymous]

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]

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.

[amitchandak]

Try first nonblank for Salesperson, sales Area code

https://docs.microsoft.com/en-us/dax/firstnonblank-function-dax

Create a matrix view with row as ID

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601