Hi,
I've currently been tasked with trying to identify distinct individuals on the UN sanction list automating the approach to upload into a sql db. However, I've struggling to incorporate the text.select function to remove the special characters from all the columns shown. I know below I've used text.selectcolumn that is to focus on one for now, but I want it to be table wide and account for any errors. Please note I'm a complete novice only learning power query yesterday.
Thanks.
Original (working)
let
Source = Xml.Tables(Web.Contents("https://scsanctions.un.org/resources/xml/en/consolidated.xml"), null, 1252),
#"Expanded INDIVIDUALS" = Table.ExpandTableColumn(Source, "INDIVIDUALS", {"INDIVIDUAL"}, {"INDIVIDUALS.INDIVIDUAL"}),
#"Expanded INDIVIDUALS.INDIVIDUAL" = Table.ExpandTableColumn(#"Expanded INDIVIDUALS", "INDIVIDUALS.INDIVIDUAL", {"FIRST_NAME", "SECOND_NAME", "THIRD_NAME", "NATIONALITY", "INDIVIDUAL_ALIAS", "INDIVIDUAL_ADDRESS", "INDIVIDUAL_PLACE_OF_BIRTH"}, {"INDIVIDUALS.INDIVIDUAL.FIRST_NAME", "INDIVIDUALS.INDIVIDUAL.SECOND_NAME", "INDIVIDUALS.INDIVIDUAL.THIRD_NAME", "INDIVIDUALS.INDIVIDUAL.NATIONALITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH"}),
#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS" = Table.ExpandTableColumn(#"Expanded INDIVIDUALS.INDIVIDUAL", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS", {"STREET", "CITY", "STATE_PROVINCE", "COUNTRY"}, {"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.STREET", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.CITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.STATE_PROVINCE", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.COUNTRY"}),
#"Expanded INDIVIDUALS.INDIVIDUAL.NATIONALITY" = Table.ExpandTableColumn(#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS", "INDIVIDUALS.INDIVIDUAL.NATIONALITY", {"VALUE"}, {"INDIVIDUALS.INDIVIDUAL.NATIONALITY.VALUE"}),
#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS" = Table.ExpandTableColumn(#"Expanded INDIVIDUALS.INDIVIDUAL.NATIONALITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS", {"QUALITY", "ALIAS_NAME", "NOTE", "DATE_OF_BIRTH", "CITY_OF_BIRTH", "COUNTRY_OF_BIRTH"}, {"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.QUALITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.ALIAS_NAME", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.NOTE", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.DATE_OF_BIRTH", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.CITY_OF_BIRTH", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.COUNTRY_OF_BIRTH"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS", each true),
#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH" = Table.ExpandTableColumn(#"Filtered Rows", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH", {"Element:Text", "STATE_PROVINCE", "COUNTRY", "CITY"}, {"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH.Element:Text", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH.STATE_PROVINCE", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH.COUNTRY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH.CITY"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH", each ([INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.STREET] <> null)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows1", {"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.STREET", "INDIVIDUALS.INDIVIDUAL.FIRST_NAME", "INDIVIDUALS.INDIVIDUAL.SECOND_NAME"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.QUALITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.NOTE", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.DATE_OF_BIRTH", "ENTITIES", "http://www.w3.org/2001/XMLSchema-instance"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([INDIVIDUALS.INDIVIDUAL.SECOND_NAME] = "KAR"))
in
#"Filtered Rows2"
Aspiration (not working)
let
Source = Xml.Tables(Web.Contents("https://scsanctions.un.org/resources/xml/en/consolidated.xml"), null, 1252),
#"Expanded INDIVIDUALS" = Table.ExpandTableColumn(Source, "INDIVIDUALS", {"INDIVIDUAL"}, {"INDIVIDUALS.INDIVIDUAL"}),
#"Expanded INDIVIDUALS.INDIVIDUAL" = Table.ExpandTableColumn(#"Expanded INDIVIDUALS", "INDIVIDUALS.INDIVIDUAL", {"FIRST_NAME", "SECOND_NAME", "THIRD_NAME", "NATIONALITY", "INDIVIDUAL_ALIAS", "INDIVIDUAL_ADDRESS", "INDIVIDUAL_PLACE_OF_BIRTH"}, {"INDIVIDUALS.INDIVIDUAL.FIRST_NAME", "INDIVIDUALS.INDIVIDUAL.SECOND_NAME", "INDIVIDUALS.INDIVIDUAL.THIRD_NAME", "INDIVIDUALS.INDIVIDUAL.NATIONALITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH"}),
#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS" = Table.ExpandTableColumn(#"Expanded INDIVIDUALS.INDIVIDUAL", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS", {"STREET", "CITY", "STATE_PROVINCE", "COUNTRY"}, {"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.STREET", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.CITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.STATE_PROVINCE", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.COUNTRY"}),
#"Expanded INDIVIDUALS.INDIVIDUAL.NATIONALITY" = Table.ExpandTableColumn(#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS", "INDIVIDUALS.INDIVIDUAL.NATIONALITY", {"VALUE"}, {"INDIVIDUALS.INDIVIDUAL.NATIONALITY.VALUE"}),
#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS" = Table.ExpandTableColumn(#"Expanded INDIVIDUALS.INDIVIDUAL.NATIONALITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS", {"QUALITY", "ALIAS_NAME", "NOTE", "DATE_OF_BIRTH", "CITY_OF_BIRTH", "COUNTRY_OF_BIRTH"}, {"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.QUALITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.ALIAS_NAME", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.NOTE", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.DATE_OF_BIRTH", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.CITY_OF_BIRTH", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.COUNTRY_OF_BIRTH"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS", each true),
#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH" = Table.ExpandTableColumn(#"Filtered Rows", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH", {"Element:Text", "STATE_PROVINCE", "COUNTRY", "CITY"}, {"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH.Element:Text", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH.STATE_PROVINCE", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH.COUNTRY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH.CITY"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_PLACE_OF_BIRTH", each ([INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.STREET] <> null)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows1", {"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ADDRESS.STREET", "INDIVIDUALS.INDIVIDUAL.FIRST_NAME", "INDIVIDUALS.INDIVIDUAL.SECOND_NAME"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.QUALITY", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.NOTE", "INDIVIDUALS.INDIVIDUAL.INDIVIDUAL_ALIAS.DATE_OF_BIRTH", "ENTITIES", "http://www.w3.org/2001/XMLSchema-instance"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([INDIVIDUALS.INDIVIDUAL.SECOND_NAME] = "KAR"))
#"Remove Special Char" = Table.SelectColumns(#"Filered Rows2", each ([INDIVIDUALS.INDIVIDUAL.FIRST_NAME]),{"A".."z","0".."9"})
in
#"Remove Special Char"
