Hello friends, Hope all is well with you! I am not an expert on PQ, but learning new things all the time. Thanks to all of you.
I am combining multi CSV files, and some of these files have undesired characters in columns, is there a way to replace these characters with "" in one shot, instead of doing the replacing one by one. Please see the below example. I want to replace: Â , ! , â , € , ™ ...etc. with "" the characters are in columns:
"Clinic Name", "PrimaryDoctorName", "PerformingDoctorName", "ProcedureName", "ProcedureCategoryName", "ProcedureGroupID2", "ProcedureSubGroup", "PatientNationality", "PatientName.
Can it be done, please help me with the new and improved code.
Thank you very much in advance!
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Â","",Replacer.ReplaceText,{"Clinic Name", "PrimaryDoctorName", "PerformingDoctorName", "ProcedureName", "ProcedureCategoryName", "ProcedureGroupID2", "ProcedureSubGroup", "PatientNationality", "PatientName"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","ï","",Replacer.ReplaceText,{"Clinic Name", "PrimaryDoctorName", "PerformingDoctorName", "ProcedureName", "ProcedureCategoryName", "ProcedureGroupID2", "ProcedureSubGroup", "PatientNationality", "PatientName"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","â","",Replacer.ReplaceText,{"Clinic Name", "PrimaryDoctorName", "PerformingDoctorName", "ProcedureName", "ProcedureCategoryName", "ProcedureGroupID2", "ProcedureSubGroup", "PatientNationality", "PatientName"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","€","",Replacer.ReplaceText,{"Clinic Name", "PrimaryDoctorName", "PerformingDoctorName", "ProcedureName", "ProcedureCategoryName", "ProcedureGroupID2", "ProcedureSubGroup", "PatientNationality", "PatientName"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","™","",Replacer.ReplaceText,{"Clinic Name", "PrimaryDoctorName", "PerformingDoctorName", "ProcedureName", "ProcedureCategoryName", "ProcedureGroupID2", "ProcedureSubGroup", "PatientNationality", "PatientName"}),
This can be done with a technique I picked-up not too long ago on
I've applied that into the attached file for a small example in a one column table and only the five characters you mentioned. Expand the FromTo table with as many characters you want.
Apply the replace step for each column. It reduces the number of steps to 1 per column, rather than the number of characters multiplied by the number of columns.
See if you can get it to work on your end.
