Help with IF Statement and transforming my Data

[Chelle]

Hello everyone, I need your help!

A newbie to PowerQueries and I was tasked to merge information from different rows into 1, as in the example shown, where the rows with the same code will be determining which ones go together:

I figured I would use an if statement to add an index that can be used as a key when values from Values 01,02 and 03 are summed, but I can't seem to get anything to work and the output that I want to happen just seems to be something I can't achieve.

Can someone please show me?

Thank you so much in advance.

Much appreciated.

[adudani · Accepted solution]

HI @Chelle,

Create a blank query (on the queries pane, right click ->new query -> other -> blank query ) and copy paste the following code into the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMDBU0lECYSMgTk2EckAoVgeHApgafArAQiAFJgbGBqZQSZCigmQ0K7AqQLYClwK4FWYGRgYgSWMgBinMyS9BswO7CmRLzICWgDgmQGwOxKV5mcQrgbgkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, NDX = _t, ValueA = _t, ValueB = _t, Value01 = _t, Value02 = _t, Value03 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"NDX", Int64.Type}, {"ValueA", Int64.Type}, {"ValueB", type text}, {"Value01", Int64.Type}, {"Value02", Int64.Type}, {"Value03", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"NDX"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Code"}, {{"Value A", each List.Min([ValueA]), type nullable number}, {"Value B", each List.Min([ValueB]), type nullable text}, {"Value01", each List.Sum([Value01]), type nullable number}, {"Value02", each List.Sum([Value02]), type nullable number}, {"Value03", each List.Sum([Value03]), type nullable number}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "NDX"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Code", "NDX", "Value A", "Value B", "Value01", "Value02", "Value03"})
in
    #"Reordered Columns"

Output:

Please accept this is as the solution if it resolves your question.

Appreciate a thumbs up if this is helpful.

[Chelle]

Thank you!  looking at it, steps can be applied instead of coding into the advanced editor. 

This has given me so much to get to where I would want to be, and it would get me a long way towards my end goal.  Thanks again.