Forum Discussion
Solved
Combine rows with offset. A2 + B1
Trying to find away to combine two rows but the combination needs to have a offset as shown below.
Can be done in M Jmiller
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8klNK1GK1QGzgzLTMyCc4ILUvJTMvHSgIFGSRJgSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Spending = _t, #"Non-Spending" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Spending", type text}, {"Non-Spending", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Spending] = "Spending")), #"Inserted Subtraction" = Table.AddColumn(#"Filtered Rows", "Subtraction", each [Index] - 1, type number), #"Merged Queries" = Table.NestedJoin(#"Inserted Subtraction",{"Subtraction"},#"Added Index",{"Index"},"Inserted Subtraction",JoinKind.LeftOuter), #"Expanded Inserted Subtraction" = Table.ExpandTableColumn(#"Merged Queries", "Inserted Subtraction", {"Non-Spending"}, {"Non-Spending.1"}), #"Inserted Merged Column" = Table.AddColumn(#"Expanded Inserted Subtraction", "Merged", each Text.Combine({[Spending], [#"Non-Spending.1"]}, "+"), type text), #"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column",{"Index", "Merged"}), #"Merged Queries1" = Table.NestedJoin(#"Added Index",{"Index"},#"Removed Other Columns",{"Index"},"Removed Other Columns",JoinKind.LeftOuter), #"Expanded Removed Other Columns" = Table.ExpandTableColumn(#"Merged Queries1", "Removed Other Columns", {"Merged"}, {"Merged"}), #"Sorted Rows" = Table.Sort(#"Expanded Removed Other Columns",{{"Index", Order.Ascending}}) in #"Sorted Rows"
4 Replies
That works if the data is in the same row but not when the row is offset.
Can be done in M Jmiller
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8klNK1GK1QGzgzLTMyCc4ILUvJTMvHSgIFGSRJgSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Spending = _t, #"Non-Spending" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Spending", type text}, {"Non-Spending", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Spending] = "Spending")), #"Inserted Subtraction" = Table.AddColumn(#"Filtered Rows", "Subtraction", each [Index] - 1, type number), #"Merged Queries" = Table.NestedJoin(#"Inserted Subtraction",{"Subtraction"},#"Added Index",{"Index"},"Inserted Subtraction",JoinKind.LeftOuter), #"Expanded Inserted Subtraction" = Table.ExpandTableColumn(#"Merged Queries", "Inserted Subtraction", {"Non-Spending"}, {"Non-Spending.1"}), #"Inserted Merged Column" = Table.AddColumn(#"Expanded Inserted Subtraction", "Merged", each Text.Combine({[Spending], [#"Non-Spending.1"]}, "+"), type text), #"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column",{"Index", "Merged"}), #"Merged Queries1" = Table.NestedJoin(#"Added Index",{"Index"},#"Removed Other Columns",{"Index"},"Removed Other Columns",JoinKind.LeftOuter), #"Expanded Removed Other Columns" = Table.ExpandTableColumn(#"Merged Queries1", "Removed Other Columns", {"Merged"}, {"Merged"}), #"Sorted Rows" = Table.Sort(#"Expanded Removed Other Columns",{{"Index", Order.Ascending}}) in #"Sorted Rows"Interesting. I will have to put some time into copying that over tonight. Not that advanced with PowerBI.
