Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
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
User | Count |
---|---|
88 | |
74 | |
69 | |
59 | |
56 |
User | Count |
---|---|
40 | |
38 | |
34 | |
32 | |
28 |