Forum Discussion
Help with IF Statement and transforming my Data
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.
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.
2 Replies
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.
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.
