Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
aritraacharya
Frequent Visitor

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:

 

NameTypeDeptSegmentPeriodYearAmountCountryCost TypeBudget'17Cost'17Budget'18Cost'18
APenHRMain2017012017352NetherlandsBudget'17352000
APenHRMain2017012017252NetherlandsCost'17025200
APenHRMain20180120181000NetherlandsBudget'180010000
APenHRMain2018012018950NetherlandsCost'18000950

 

But we want to convert the data like this:

NameTypeDeptSegmentPeriodYearCountryBudgetCostBudget last yearCost Last year
APenHRMain2017012017Netherlands35225200
APenHRMain2018012018Netherlands1000950352252
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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"

 

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@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"

 

 

 


Regards
Zubair

Please try my custom visuals

Thank you. This worked for me.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.