The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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
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.
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.