Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |