WHERE statement

[Babinsky]

Hello Everyone, I'm trying to obtain the following information.  

SELECT * FROM file

WHERE field LIKE '6980%'

And then produce a new field.  Am I missing something in the syntax?  Thanks!

- Babinsky

[Phil_Seamark]

Of course if you are using SQL you could change your statement to be something like

SELECT * , CASE WHEN field LIKE '6980% THEN something ELSE somethingelse END As MyNewCol

FROM file

[Phil_Seamark]

Another lazy option would be to use the Power Query [Add conditional Column] where you have access to a GUI based rule builder which offers options such as "Contains" to help populate a new column.

[Greg_Deckler · Accepted solution]

One way in "M" would be to use Text.StartsWith like so:

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\like.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each if Text.StartsWith([column2],"6980") then 1 else 0),
    Custom = #"Added Custom"{0}[Custom]
in
    Custom

Make sure you column (column2) is Text and then you can filter the resulting column to just 1.