hola
Actualmente se me ha encargado tratar de identificar a individuos distintos en la lista de sanciones de la ONU automatizando el enfoque para subir a un sql db. Sin embargo, he luchado para incorporar la función text.select para eliminar los caracteres especiales de todas las columnas mostradas. Sé a continuación que he utilizado text.selectcolumn que es centrarse en uno por ahora, pero quiero que sea de toda la tabla y tenga en cuenta cualquier error. Tenga en cuenta que soy un principiante completo sólo aprendizaje consulta de poder ayer.
gracias.
Original (en funcionamiento)
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"
Aspiración (no funciona)
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"
