Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous

 

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 @Anonymous

 

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

@Anonymous

 

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
Anonymous
Not applicable

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 🙂

@AnonymousThis 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.