In this video I show how to make a Merge with partial match in PQ.
Function Code
let
Source = (Text as text) => let
Source = FromTo,
#"Replaced Value" = Table.ReplaceValue(Source,null,"##@@",Replacer.ReplaceValue,{"Separator"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each Text.Contains(Text, [Description])),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Numeber", each if [Trype] = "Sales" or [Trype] = "Shop" then Text.Split(Text.End(Text, Text.Length(Text) - Text.Length([Description])), [Separator]){0} else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "People", each if [Trype] = "Sales" or [Trype] = "Shop" then try Text.Split(Text.End(Text, Text.Length(Text) - Text.Length([Description])), [Separator]){1} otherwise null else null),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Trype", "Numeber", "People"}),
Custom1 = Table.First(#"Removed Other Columns")
in
Custom1
in
Source