Forum Discussion
lasersharks
4 years agoKudo Collector
Multi Step Merge
Hi all, I have a tricky one that I'd appreciate some advice on. I'm trying to perform a multi step merge between two tables. I have come up with two solutions which are described below with sampl...
lasersharks
4 years agoKudo Collector
Thanks AlexisOlson I have tried with normal if then else statements and it doesn't improve things much. Method 1 works and I even have a setup where I filter subsequent merges if a match has already happened but it isn't fast by any measure. I was wondering if there was some other technique out there I hadn't considered.
- AlexisOlson4 years agoSuper User
You could also try unpivoting both tables before merging.
I don't know if this will be any faster but you can try something like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjRU0lFSyMvPK04FIgU4QBZVitWJVoJzdJScjHDKYtEN4iQbo5uhgJUNUgRUi08aSSIZtzvQjHA2RJdNMoQ6KxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Column", "Value"), #"Uppercased Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Upper, type text}}), #"Merged Queries" = Table.NestedJoin(#"Uppercased Text", {"Column", "Value"}, Table1, {"Column", "Value"}, "Table", JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Ref"}, {"Ref"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Table", each ([Ref] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Index"}, {{"Ref", each List.First([Ref]), type text}}), #"Merged Queries1" = Table.NestedJoin(#"Added Index", {"Index"}, #"Grouped Rows", {"Index"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Grouped Rows", {"Ref"}, {"Ref"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"Index"}) in #"Removed Columns"- lasersharks4 years agoKudo Collector
Thanks AlexisOlson . I will give it a try tomorrow and see if the performance improves.