Forum Discussion
Solved
Power query - Add string if identical string found above
Hi, Im fairly new to this game so sorry if this is not a good question. I have this huge dataset with multiple excel sheets that are structured with date and operation as seen below: Date ...
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67DcAwCEXRXagtOTz8ScrEY1jef404BVKA9kg87pyEzBkHQIluWukD8dA9MIIUlUfl8gIOUlWGSrMiuXio9rf4PAl5W8SvcJjhZvO29CDn72q9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operation = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Operation", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Operation"}, {{"Temp", each _, type table [Date=nullable date, Operation=nullable text, Index=number]}}), //Function Start fxProcess=(Tbl)=> let #"Added Index" = Table.AddIndexColumn(Tbl, "Index.1", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try [Index]-#"Added Index"[Index]{[Index.1]-1} otherwise 2), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] > 1)), #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index.2", 1, 1, Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Index1",{"Index", "Index.1", "Custom"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Operation modified", each if [Index.2]=1 then [Operation] else [Operation]&" ("&Text.From([Index.2])&")"), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index.2"}) in #"Removed Columns1", //Function End #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])), #"Expanded fxProcess" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcess", {"Date", "Operation modified"}, {"Date", "Operation modified"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}), #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Operation", "Date"}, #"Removed Columns", {"Operation", "Date"}, "Removed Columns", JoinKind.LeftOuter), #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Operation modified"}, {"Operation modified"}), #"Filled Down" = Table.FillDown(#"Expanded Removed Columns",{"Operation modified"}) in #"Filled Down"
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalSee the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67DcAwCEXRXagtOTz8ScrEY1jef404BVKA9kg87pyEzBkHQIluWukD8dA9MIIUlUfl8gIOUlWGSrMiuXio9rf4PAl5W8SvcJjhZvO29CDn72q9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operation = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Operation", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Operation"}, {{"Temp", each _, type table [Date=nullable date, Operation=nullable text, Index=number]}}),
//Function Start
fxProcess=(Tbl)=>
let
#"Added Index" = Table.AddIndexColumn(Tbl, "Index.1", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try [Index]-#"Added Index"[Index]{[Index.1]-1} otherwise 2),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] > 1)),
#"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index.2", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index1",{"Index", "Index.1", "Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Operation modified", each if [Index.2]=1 then [Operation] else [Operation]&" ("&Text.From([Index.2])&")"),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index.2"})
in
#"Removed Columns1",
//Function End
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
#"Expanded fxProcess" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcess", {"Date", "Operation modified"}, {"Date", "Operation modified"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Operation", "Date"}, #"Removed Columns", {"Operation", "Date"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Operation modified"}, {"Operation modified"}),
#"Filled Down" = Table.FillDown(#"Expanded Removed Columns",{"Operation modified"})
in
#"Filled Down"
WOW thanks.
That did work. I am just a humble physicist, and my usual strategy is to throw enough stuff on the wall until something sticks. Its infinetly better than anything I could put together. I did one minor adjusment, and added a line to align the date format with my system. Im one european date format. Pasted my adjustment below if anybody is interested! Thanks Vijay
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc67DcAwCEXRXagtOTz8ScrEY1jef404BVKA9kg87pyEzBkHQIluWukD8dA9MIIUlUfl8gIOUlWGSrMiuXio9rf4PAl5W8SvcJjhZvO29CDn72q9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operation = _t]),
#"Change Type" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
#"Changed Type1" = Table.TransformColumnTypes(#"Change Type",{{"Date", type date}, {"Operation", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Operation"}, {{"Temp", each _, type table [Date=nullable date, Operation=nullable text, Index=number]}}),
//Function Start
fxProcess=(Tbl)=>
let
#"Added Index" = Table.AddIndexColumn(Tbl, "Index.1", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try [Index]-#"Added Index"[Index]{[Index.1]-1} otherwise 2),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] > 1)),
#"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index.2", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index1",{"Index", "Index.1", "Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Operation modified", each if [Index.2]=1 then [Operation] else [Operation]&" ("&Text.From([Index.2])&")"),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index.2"})
in
#"Removed Columns1",
//Function End
#"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
#"Expanded fxProcess" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxProcess", {"Date", "Operation modified"}, {"Date", "Operation modified"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded fxProcess",{"Temp"}),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Operation", "Date"}, #"Removed Columns", {"Operation", "Date"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Operation modified"}, {"Operation modified"}),
#"Filled Down" = Table.FillDown(#"Expanded Removed Columns",{"Operation modified"})
in
#"Filled Down"