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

vicky_'s avatar
vicky_
Icon for Super User rankSuper 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.