Combine rows with offset. A2 + B1

[Jmiller]

Trying to find away to combine two rows but the combination needs to have a offset as shown below. 

[smpa01 · Accepted solution]

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"

[Jmiller]

Interesting. I will have to put some time into copying that over tonight. Not that advanced with PowerBI. 

[Anonymous]

You should be able to combine them with the below function:

Column = CONCATENATE(Sheet1[Speeding], CONCATENATE(", ", Sheet1[Vehicle.Count]))

[Jmiller]

That works if the data is in the same row but not when the row is offset.