Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
.
Solved! Go to Solution.
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
Thanks @Anonymous
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
63 | |
53 | |
39 | |
25 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |