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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
F75
Advocate I
Advocate I

Concatenate row values based on column condition

Hi, I want to concatenate current value with next row value if next row starts with 'TAX' . 'TAX' occurs randomly in the data.

 

Appreciate your help.


.temp1.png

1 ACCEPTED SOLUTION
Krutigawale33
Responsive Resident
Responsive Resident

Hello @F75 ,

 

you can achieve this in power query editor.

load the in put data in query editor and follow the steps:

let
Source = Excel.Workbook(File.Contents("C:\Users\kgawale\Desktop\Test.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, Sheet2, {"Index"}, "Sheet2", JoinKind.LeftOuter),
#"Expanded Sheet2" = Table.ExpandTableColumn(#"Merged Queries", "Sheet2", {"Column1", "Column2"}, {"Sheet2.Column1", "Sheet2.Column2"}),
#"Added Custom" = Table.AddColumn(#"Expanded Sheet2", "Custom", each if [Sheet2.Column1]="TAX" then [Column2]&" "&[Sheet2.Column2] else [Column2]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2", "Index", "Sheet2.Column1", "Sheet2.Column2"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column1] = "TOTAL"))
in
#"Filtered Rows"

 

let
Source = Excel.Workbook(File.Contents("C:\Users\kgawale\Desktop\Test.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type)
in
#"Added Index"

 

 

you can see the transform data of my powerbi file here pbi file 

 

View solution in original post

2 REPLIES 2
F75
Advocate I
Advocate I

Thanks @Krutigawale33 

Krutigawale33
Responsive Resident
Responsive Resident

Hello @F75 ,

 

you can achieve this in power query editor.

load the in put data in query editor and follow the steps:

let
Source = Excel.Workbook(File.Contents("C:\Users\kgawale\Desktop\Test.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, Sheet2, {"Index"}, "Sheet2", JoinKind.LeftOuter),
#"Expanded Sheet2" = Table.ExpandTableColumn(#"Merged Queries", "Sheet2", {"Column1", "Column2"}, {"Sheet2.Column1", "Sheet2.Column2"}),
#"Added Custom" = Table.AddColumn(#"Expanded Sheet2", "Custom", each if [Sheet2.Column1]="TAX" then [Column2]&" "&[Sheet2.Column2] else [Column2]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2", "Index", "Sheet2.Column1", "Sheet2.Column2"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Column1] = "TOTAL"))
in
#"Filtered Rows"

 

let
Source = Excel.Workbook(File.Contents("C:\Users\kgawale\Desktop\Test.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type)
in
#"Added Index"

 

 

you can see the transform data of my powerbi file here pbi file 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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