Forum Discussion
Solved
Filldown Query when input incorrect Value
How to FIlldown query input incorrect value to correct with same value
Thank you!
- change address to your source excel file in Source step.
- if you want to add more CharsToRemove you can do it here
- 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
6 Replies
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
Thanhs for reply
This is sample data: https://docs.google.com/spreadsheets/d/1Ehs0zmWVGZ4YyTAAAVWsgvhAIZLBIgZZ/edit?usp=sharing&ouid=106724302240185267318&rtpof=true&sd=trueWhat about expected result based on sample data? How do I know which value is incorrect?
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=106724302240185267318&rtpof=true&sd=true
Could you please have anyone help me?
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.
- change address to your source excel file in Source step.
- if you want to add more CharsToRemove you can do it here
- 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
