Filldown Query when input incorrect Value

LucTP,

1. change address to your source excel file in Source step.
2. if you want to add more CharsToRemove you can do it here
   
3. if you want to add more StringsToReplace add them here
   

    

   There are still some differences like theese but it is hard to catch everything
   
   
   

    

    

let
    Source = Excel.Workbook(File.Contents("Address\Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    CharsToRemove = ",.""-<>()[]{}",
    CharsToReplace = List.Buffer({{"&", "AND"}, {"LIMITED", "LTD"}, {"LIMITTED", "LTD"}}),
    StepBack = PromotedHeaders,
    Ad_CompanyCleaned = Table.AddColumn(StepBack, "Company Cleaned", each 
      [ removeChars = List.Select(Text.SplitAny([Company], CharsToRemove & " "), (x)=> not List.Contains(Text.ToList(CharsToRemove) & {"", " "}, x)),
        replaceChars = List.ReplaceMatchingItems(removeChars, CharsToReplace),
        trimEndS = List.Transform(replaceChars, (x)=> Text.TrimEnd(x, {"S", "s"})),
        combine = Text.Combine(trimEndS, " ")
      ][combine], type text),
    GroupedRowsCheck = Table.Group(Ad_CompanyCleaned, {"Company Cleaned"}, {{"1", each 1}}),
    RemovedColumns1 = Table.RemoveColumns(GroupedRowsCheck,{"1"}),
    MergedQueries = Table.FuzzyNestedJoin(RemovedColumns1, {"Company Cleaned"}, Ad_CompanyCleaned, {"Company Cleaned"}, "GroupedRowsCheck", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.8]),
    Ad_CompanyCommonName = Table.AddColumn(MergedQueries, "Company Common Name", each [GroupedRowsCheck]{0}[Company Cleaned], type text),
    RemovedColumns2 = Table.RemoveColumns(Ad_CompanyCommonName,{"GroupedRowsCheck"}),
    MergedQueries2 = Table.NestedJoin(Ad_CompanyCleaned, {"Company Cleaned"}, RemovedColumns2, {"Company Cleaned"}, "RemovedColumns1", JoinKind.LeftOuter),
    ExpandedRemovedColumns1 = Table.ExpandTableColumn(MergedQueries2, "RemovedColumns1", {"Company Common Name"}, {"Company Common Name"}),
    RemovedColumns3 = Table.RemoveColumns(ExpandedRemovedColumns1,{"Company Cleaned"})
in
    RemovedColumns3