I have a large data set (A) that I need to merge with another (smaller) table (B) based on the following conditions: If the first letter of a cell in Column (X) from the table (A) matches the content in the cell, column (Y) from the table (B) then match/merge. Else, check if the first two letters from the cell in column (X), table (A), matches cell content in column (Y), table (B) then Match/merge. Note that Column (Y) in table B has UNIQUE Values; Either on letter (Alphabet) or two letters.

Here is an example: [An example]

Tables

CodePudding user response：

I think this might provide the result you are looking for. It does not do a straightforward single condition-based merge. It uses a combination of merges: one between TableA and TableB based on the first character of each row of Column X of TableA and the values of each row in Column Y in TableB, and another based on the first two characters of each row of Column X in TableA and the values of each row in Column Y of TableB. It expands each merge's resulting column to show only the Column Y values of the merge in the column. Then it merges the results of those two merge results' columns into one column and uses that column to merge with Column Y of TableB again, to pull in the rest of the Column Y values.

let
    Source = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column X", type text}, {"Column X 2", type number}, {"Column X 3", type number}, {"Column X 4", type number}, {"Column X 5", type number}, {"Column X 6", type number}, {"Column X 7", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Column X First 1", each Text.Start([Column X],1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column X First 2", each Text.Start([Column X],2)),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Column X First 1"}, TableB, {"Column Y"}, "Column Y First 1", JoinKind.LeftOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "Column Y First 1", {"Column Y"}, {"Column Y First 1"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded TableB", {"Column X First 2"}, TableB, {"Column Y"}, "Column Y First 2", JoinKind.LeftOuter),
    #"Expanded TableB1" = Table.ExpandTableColumn(#"Merged Queries1", "Column Y First 2", {"Column Y"}, {"Column Y First 2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded TableB1", {{"Column Y First 2", type text}}, "en-US"),{"Column Y First 1", "Column Y First 2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column Y Match"),
    #"Merged Queries2" = Table.NestedJoin(#"Merged Columns", {"Column Y Match"}, TableB, {"Column Y"}, "TableB", JoinKind.LeftOuter),
    #"Expanded TableB2" = Table.ExpandTableColumn(#"Merged Queries2", "TableB", {"Column 1", "Column 2", "Column 3", "Column Y"}, {"Column 1", "Column 2", "Column 3", "Column Y"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded TableB2",{"Column X First 1", "Column X First 2", "Column Y Match"})
in
    #"Removed Columns"

CodePudding user response：

Thank you for taking the time to help with this question. I really appreciate it. I think I did something similar but did not write a code like yours. Instead, I created two columns in the first table (A), one of which has all the first letters from column (X), and the other has all the first two letters (from column (X). Next, I merged based on the following steps: Merged table (A) and Table (B) based on the new column (One letter from Column (X) and the column of alphabets from table (B). Subsequently, I removed (filtered out) all the "null" values from the new Merge table (M1). Went back to table (A) and merged based on the column with two letters and the alphabet column from table (B). Cleaned all the "Null" values (removed them). Now I have a new merged table (M2). Lastly, I did an Append operation so the tables (Merge M1) and (Merge M2) are combined. I did notice some duplications, like a couple of rows were duplicated. I couldn't find out why. Does this process make any sense or does it have redundencies? I am trying to learn how code in M to avoid any errors