Filldown Query when input incorrect Value

[LucTP]

 

How to FIlldown query input incorrect value to correct with same value 
Thank you!

[LucTP]

Hi everyone,

I have a question about power Bi clean data, the data is not correct as this sample

Name	Address
CENSEA INC
CENSEA INC.
CENTRAL COLDSTORAGE KUCHING SDN. BHD
CENTRAL COLDSTORAGE KUCHING SDN. BHD.,
CENTRO CUESTA NACIONAL
CENTRO CUESTA NACIONAL.,
CENTRO CUESTA NACIONAL.,

result correct data

Name	Address
CENSEA INC
CENSEA INC
CENTRAL COLDSTORAGE KUCHING SDN. BHD
CENTRAL COLDSTORAGE KUCHING SDN. BHD
CENTRO CUESTA NACIONAL
CENTRO CUESTA NACIONAL
CENTRO CUESTA NACIONAL

this is data : https://docs.google.com/spreadsheets/d/1Ehs0zmWVGZ4YyTAAAVWsgvhAIZLBIgZZ/edit?usp=sharing&ouid=10672...

 Could you please have anyone help me?

[dufoq3]

I've chcecked your data roughly. It seems you need remove extra spaces and some characters like , and .

I'll create it for you when I come home. Probably within next two hours.

[dufoq3 · Accepted solution]

@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

[dufoq3]

You have to identify incorrect rows (add seperate column) and then replace incorrect rows with null. Then you can apply fill down. If you don't know how to do it. Provide sample data (as table so we can copy/paste) and also expected resu.t

[LucTP]

Thanhs for reply 
This is sample data: https://docs.google.com/spreadsheets/d/1Ehs0zmWVGZ4YyTAAAVWsgvhAIZLBIgZZ/edit?usp=sharing&ouid=10672... 

[dufoq3]

What about expected result based on sample data? How do I know which value is incorrect?