cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sk2019
Frequent Visitor

Import only Non English contained rows and blanks and delete remaining

I have an excel sheet as below and i want to import only chines names and blanks into power bi and delete remaining rows.

 

Excel Sheet data:

-----------------

 

IDName
1shiv
2kumar
3 24567
4谢谢
5你好
6明天见
7 

 

Data in Power BI should look like:

---------------------------------------

 

IDName
4谢谢
5你好
6明天见
7 

 

in the given example above i have shown few records but i have millions of rows to do this.

1 ACCEPTED SOLUTION

@sk2019

 

Sorry I had missed the null

 

File attached as well

 

try this

 

= Table.SelectRows(#"Changed Type", each List.ContainsAny({"",null}, {Text.Select([Name],List.Transform({1..126}, each Character.FromNumber(_)))}))

Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

Hi @sk2019

 

Try adding this step in Query Editor

 

It works with your sample data

 

= Table.SelectRows(#"Changed Type", each Text.Select([Name],List.Transform({1..126}, each Character.FromNumber(_)))="")

 

See the attached file


Regards
Zubair

Please try my custom visuals

@sk2019

 

Sorry I had missed the null

 

File attached as well

 

try this

 

= Table.SelectRows(#"Changed Type", each List.ContainsAny({"",null}, {Text.Select([Name],List.Transform({1..126}, each Character.FromNumber(_)))}))

Regards
Zubair

Please try my custom visuals

View solution in original post

Thank you so much @Zubair_Muhammad it worked.

However, i didnt get this trasform List.Transform({1..126) that how it works?. if possible, pls explain.

Thank you again for swift response and help 🙂

@sk2019This function results in a list of majority of characters that we can type from the keyboard

 

List.Transform({1..126}, each Character.FromNumber(_))

 

Create a blank query and cope paste above code you will get

 

china.png

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.