Merge Multiple rows in one row

Anonymous

Try this

This works with your sample data

File attached as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpIzQOSHkFAwjcxE8Q2MjA0NzCEMoCUsakRkPRLLclILcpJzEspBvKcSlPSU0tiSg0MjMyRFBnAcawOscYbYTHeOb8Y2XADuDIiDLeAGW4BpAwNDAzwO94CydlQ1aRYYGmKaT6S65FNN4Aqj40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Type = _t, Dept = _t, Segment = _t, Period = _t, Year = _t, Amount = _t, Country = _t, #"Cost Type" = _t, #"Budget'17" = _t, #"Cost'17" = _t, #"Budget'18" = _t, #"Cost'18" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Type", type text}, {"Dept", type text}, {"Segment", type text}, {"Period", Int64.Type}, {"Year", Int64.Type}, {"Amount", Int64.Type}, {"Country", type text}, {"Cost Type", type text}, {"Budget'17", Int64.Type}, {"Cost'17", Int64.Type}, {"Budget'18", Int64.Type}, {"Cost'18", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Name", "Type", "Dept", "Segment", "Period", "Year", "Country", "Amount", "Cost Type", "Budget'17", "Cost'17", "Budget'18", "Cost'18"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Name", "Type", "Dept", "Segment", "Period", "Year", "Country"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Budget", each Table.SelectRows([All],each Text.Contains([Cost Type], "Budget"))[Amount]{0}),
    AddedCustom1 = Table.AddColumn(#"Added Custom", "Cost", each Table.SelectRows([All],each Text.Contains([Cost Type], "Cost"))[Amount]{0}),
    #"Added Custom1" = Table.AddColumn(AddedCustom1, "Budget LastYear", each let myyear=[Year] in 
Table.SelectRows(AddedCustom1,each [Year]=myyear-1)[Budget]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Budget LastYear", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values", "Cost Last Year", each let myyear=[Year] in 
Table.SelectRows(AddedCustom1,each [Year]=myyear-1)[Cost]),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom2", {"Cost Last Year", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values1"