Forum Discussion

marcura's avatar
marcura
Helper II
7 years ago
Solved

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:

 

 

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




I am a power bi padawan yet.

General Comment
Need Help
Show and Tell
Tutorial Requests

4 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      marcura
      Helper II
      7 years ago

      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?