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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors