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.
I am new to Power Query and using Power Query to pull data in from an external file. The data has multiple columns. The two columns, Date and Mine, are the ones that I want to use to create a new conditional column from. The value for Mine needs to be changed for one value based on whether it is before or after a certain date.
An example of the table looks like this
I tried the following to create a new column that I would then use to replace the Existing "Mine" column:
= Table.AddColumn(#"Removed Columns", "Mine1", each if [Mine] <> "OBO" then [Mine] else if [Mine] = "OBO" and [Date] < 01/04/2024 then "OBC" else "OBO")
However the code above produces an Expression.Error
Expression.Error: We cannot apply operator < to types Number and Date.
Details:
Operator=<
Left=0.001482213
Right=1/05/2024
I am hoping I just have a syntax error but any help is much appreciated.
Solved! Go to Solution.
use #date to create dates like #date(2024, 04, 01) for April 1st, 2024. Now you compare [Date] value with a result of division of 1 by 4 by 2024 (or whatever you do in your expression) - those are of different types which is not allowed in Power Query M.
Thanks the #date(yyyy,mm,dd) format worked.
=Table.ReplaceValue(#"Removed Columns",each [Date],null,(x,y,z)=>if x="OBO" and y<#date(2024,4,1) then "OBC" else x,{"Mine"})
use #date to create dates like #date(2024, 04, 01) for April 1st, 2024. Now you compare [Date] value with a result of division of 1 by 4 by 2024 (or whatever you do in your expression) - those are of different types which is not allowed in Power Query M.
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.