Forum Discussion
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!
- Babinsky
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 CustomMake sure you column (column2) is Text and then you can filter the resulting column to just 1.
3 Replies
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 CustomMake sure you column (column2) is Text and then you can filter the resulting column to just 1.
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.
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
