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.
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"
Solved! Go to Solution.
Hi @rickyjassal ,
Is it like this?
If not, please provide sample data and expected output.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rickyjassal ,
Has your problem been solved?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rickyjassal ,
Is it like this?
If not, please provide sample data and expected output.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |