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