Forum Discussion
Merge Multiple rows in one row
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 |
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"
2 Replies
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"