Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Babinsky
Helper I
Helper I

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

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Phil_Seamark
Microsoft Employee
Microsoft Employee

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark
Microsoft Employee
Microsoft Employee

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Greg_Deckler
Community Champion
Community Champion

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.