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

Extract only text (with accent) from a number/string column

Hello everyone, forum noob here.

Someone can give me a tip on how to extract only the text (with accent) from that column:

 

Screenshot_1.png

 

The top 2 lines (number) (duration) will be useless for me, also in some cases the text can fill up to 2 rows instead of only one, like the example below


Screenshot_2.png

I am a power bi padawan yet.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Extract only text (with accent) from a number/string column

I made a short video showing how to integrate M-code into your solution here: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-c... 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




4 REPLIES 4
Super User
Super User

Re: Extract only text (with accent) from a number/string column

Hi @marcura,

please define some rules which should filter out all unwanted lines. You know best your data. Then I can rewrite these rules in PQ.

Highlighted
EtienneOL Member
Member

Re: Extract only text (with accent) from a number/string column

Hi @marcura ,

Could you try with this query : 

let
    Source = YourTable,
#"Duplicated Column" = Table.DuplicateColumn(Source, "Mesclado", "Mesclado2"), #"Filtered Rows" = Table.SelectRows(#"Duplicated Column", each not Text.Contains([#"Mesclado2"], "->")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Mesclado2", type number}}), #"Kept Errors" = Table.SelectRowsWithErrors(#"Changed Type", {"Mesclado2"}), #"Removed Columns" = Table.RemoveColumns(#"Kept Errors",{"Mesclado2"}) in #"Removed Columns"

 

marcura Frequent Visitor
Frequent Visitor

Re: Extract only text (with accent) from a number/string column

I already have this code:

let
Source = Csv.Document(File.Contents("C:\Users\João.Marçura\Downloads\Subtitles\[LilSubs.com]__Portuguese.txt"),[Delimiter="#(tab)", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Mesclado"}}),
#"Removed Blank Rows" = Table.SelectRows(#"Renamed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"

How can i integrate both? Also the column "Mesclado2" will generate from the code or it must be created before?

Super User
Super User

Re: Extract only text (with accent) from a number/string column

I made a short video showing how to integrate M-code into your solution here: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-c... 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 284 members 3,112 guests
Please welcome our newest community members: