Merge Multiple rows in one row

[aritraacharya]

My data source is excel.

Once loaded in power BI I want to do this . The data is now like this:

Name	Type	Dept	Segment	Period	Year	Amount	Country	Cost Type	Budget'17	Cost'17	Budget'18	Cost'18
A	Pen	HR	Main	201701	2017	352	Netherlands	Budget'17	352	0	0	0
A	Pen	HR	Main	201701	2017	252	Netherlands	Cost'17	0	252	0	0
A	Pen	HR	Main	201801	2018	1000	Netherlands	Budget'18	0	0	1000	0
A	Pen	HR	Main	201801	2018	950	Netherlands	Cost'18	0	0	0	950

But we want to convert the data like this:

Name	Type	Dept	Segment	Period	Year	Country	Budget	Cost	Budget last year	Cost Last year
A	Pen	HR	Main	201701	2017	Netherlands	352	252	0	0
A	Pen	HR	Main	201801	2018	Netherlands	1000	950	352	252

[Zubair_Muhammad · Accepted solution]

@aritraacharya

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"

[aritraacharya]

Thank you. This worked for me.