Forum Discussion

Gully's avatar
Gully
Frequent Visitor
3 years ago
Solved

Excluding bad data from a new column

Hi,

I have a column of sharepoint data in PowerBI containing page titles. However, some rows have junk in it e.g. 90ArFg1d.

How do I create a new column without the junk (in bold) in it ?

For example:.

ColumnA

10 Tips for Web hosting

Who's Who

Complaints Procedures

90ArFg1d

Transport Department

15HTy6Gh

Commercial Department

 

I can only think of having an If statement which tests ColumnA for values of  8 characters long and starting with a number. However, I am struggling to write that as I am new to PowerBI.

Is there a better solution ?

 

Any advice would be appreciated. 

 

Need Help
  • vicky_'s avatar
    vicky_
    3 years ago

    If following your rules, in Power Query, try the following: 
    = Table.SelectRows(#"Filtered Rows", each not (Value.Is(Value.FromText(Text.Start([Column1], 2)), type number) and Text.Length([Column1]) = 8))

     

    However, if possible, I would suggest trying to clean the data somehow before it gets to you like this. I imagine that there might be some cases that aren't junk, but will get caught in the above conditions.

1 Reply

  • vicky_'s avatar
    vicky_
    Super User
    3 years ago

    If following your rules, in Power Query, try the following: 
    = Table.SelectRows(#"Filtered Rows", each not (Value.Is(Value.FromText(Text.Start([Column1], 2)), type number) and Text.Length([Column1]) = 8))

     

    However, if possible, I would suggest trying to clean the data somehow before it gets to you like this. I imagine that there might be some cases that aren't junk, but will get caught in the above conditions.