cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

filtering out any text values in a column in query editor / m

I am splitting a column from sharepoint with different directories, some of these directories are text values and some are only number i.e like year 2018,  i am only interested in the numbers, ie year -  how can i filter out all text values or only select all the numerical values?  is this possible? I know how to do it manually by unticking what i dont want, however this is for future proofing.


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: filtering out any text values in a column in query editor / m

Hi @vanessafvg

 

11.png   -> 12.png

 

Creates a table with three rows. The first row's data type is number. The second and third rows are both text.

Then creates two new columns showing if each row is a text or number type. The first column checks the data type.

 

Code in Advanced Editor

let
    Source = Table.FromRecords({[A=2017],[A="b"],[A="a"]}),
    #"Added Custom" = Table.AddColumn(Source, "Type", each
      let
        TypeLookup = (inputType as type) as text =>
          Table.FromRecords(
            {
              [Type=type text, Value="Text"],
              [Type=type number, Value="Number"]
            }
        ){[Type=inputType]}[Value]
      in
        TypeLookup(Value.Type([A]))
    ),
    textvalue=Table.SelectRows(#"Added Custom",each[Type]="Text")
in
    textvalue

 

 

Best Regards

Maggie

3 REPLIES 3
Community Support Team
Community Support Team

Re: filtering out any text values in a column in query editor / m

Hi @vanessafvg

 
Code in Advanced Editor
 
 
let
    CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\PR4.0\4.26\test out.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Text.Remove([Column1],CharsToRemove))
in
#"Added Custom"
 
Results:
pastedImage.png
Text.Remove(text as nullable text,removeChars as any)
The first parameter should be pretty easy, we could just feed in the [Column1] column,
The second parameter - removeChars is an “any” datatype which is not restricted to a single character, so we need to create a list of the characters to remove.
CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_))
 
Best Regards
Maggie
Super User
Super User

Re: filtering out any text values in a column in query editor / m

@v-juanli-msft  thanks so much for that but what i meant was filtering out the rows that have text value as opposed to remove text values from columns if you know what i mean?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Community Support Team
Community Support Team

Re: filtering out any text values in a column in query editor / m

Hi @vanessafvg

 

11.png   -> 12.png

 

Creates a table with three rows. The first row's data type is number. The second and third rows are both text.

Then creates two new columns showing if each row is a text or number type. The first column checks the data type.

 

Code in Advanced Editor

let
    Source = Table.FromRecords({[A=2017],[A="b"],[A="a"]}),
    #"Added Custom" = Table.AddColumn(Source, "Type", each
      let
        TypeLookup = (inputType as type) as text =>
          Table.FromRecords(
            {
              [Type=type text, Value="Text"],
              [Type=type number, Value="Number"]
            }
        ){[Type=inputType]}[Value]
      in
        TypeLookup(Value.Type([A]))
    ),
    textvalue=Table.SelectRows(#"Added Custom",each[Type]="Text")
in
    textvalue

 

 

Best Regards

Maggie