Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
154 | |
120 | |
73 | |
73 | |
63 |