Forum Discussion
Anonymous
4 years agoNot applicable
Merge two rows based on conditions using Power Query
Hi, I'm hoping that somebody on here can help me as I've been unable to find a way to do this in Power Query and don't know enough about M Code to be able to obtain my desired solution. I've attach...
- 4 years ago
Hi Anonymous ,
please check the enclosed file for the solution as well:// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}, {"Column2", type text}, {"Column3", type text}, {"Column4", type datetime}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "EntryIndex", each if [Column5] = null and [Column6] = null then null else [Index]), #"Filled Up" = Table.FillUp(#"Added Custom",{"EntryIndex"}), #"Replaced Value1" = Table.ReplaceValue(#"Filled Up",null, each [Index],Replacer.ReplaceValue,{"EntryIndex"}), #"Filled Down" = Table.FillDown(#"Replaced Value1",{"Column1", "Column2"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"EntryIndex"}, {{"Column1", each List.Min([Column1]), type nullable datetime}, {"Column2", each List.Min([Column2]), type nullable text}, {"Column5", each List.Sum([Column5]), type nullable number}, {"Column6", each List.Sum([Column6]), type nullable number}, {"Column3", each Text.Combine(_[Column3], " ")}}), #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"Column5", "Column6"}), #"Inserted Addition" = Table.AddColumn(#"Replaced Value", "Value", each [Column6] - [Column5], type number), Custom1 = fnRunningTotal( #"Inserted Addition", "EntryIndex", "Value") in Custom1
ImkeF
4 years agoCommunity Champion
Hi Anonymous ,
please check the enclosed file for the solution as well:
// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}, {"Column2", type text}, {"Column3", type text}, {"Column4", type datetime}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "EntryIndex", each if [Column5] = null and [Column6] = null then null else [Index]),
#"Filled Up" = Table.FillUp(#"Added Custom",{"EntryIndex"}),
#"Replaced Value1" = Table.ReplaceValue(#"Filled Up",null, each [Index],Replacer.ReplaceValue,{"EntryIndex"}),
#"Filled Down" = Table.FillDown(#"Replaced Value1",{"Column1", "Column2"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"EntryIndex"}, {{"Column1", each List.Min([Column1]), type nullable datetime}, {"Column2", each List.Min([Column2]), type nullable text}, {"Column5", each List.Sum([Column5]), type nullable number}, {"Column6", each List.Sum([Column6]), type nullable number}, {"Column3", each Text.Combine(_[Column3], " ")}}),
#"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"Column5", "Column6"}),
#"Inserted Addition" = Table.AddColumn(#"Replaced Value", "Value", each [Column6] - [Column5], type number),
Custom1 = fnRunningTotal( #"Inserted Addition", "EntryIndex", "Value")
in
Custom1