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
vanessafvg
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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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.