Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How to FIlldown query input incorrect value to correct with same value
Thank you!
Solved! Go to Solution.
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
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?
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
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=10672...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.