Forum Discussion

Tony67's avatar
Tony67
New Member
2 years ago
Solved

How do I create a conditional column by comparing data from two existing columns?

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.

 

  • 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. 

3 Replies

  • 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. 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    =Table.ReplaceValue(#"Removed Columns",each [Date],null,(x,y,z)=>if x="OBO" and y<#date(2024,4,1) then "OBC" else x,{"Mine"})