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
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
Anonymous
Not applicable

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

Anonymous
Not applicable

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
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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.