Forum Discussion

Babinsky's avatar
Babinsky
Helper I
9 years ago
Solved

WHERE statement

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!  ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    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.

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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