Excel Query if

[th081]

Hello,

This is probably a basic question but i cant seem to get it. It is my first foray into Power Query in Excel.

I have imported a table in Power Query, the table has a Date_Time column and a STAT column the STAT column has a 1 or 0. The 1 represents a start time and the 0 the end time.

I have inserted a conditional column and wrote a if : if [STAT] = 0 then [Date_Time] else if [STAT] = 1 then null else null

expecting to see the end time from the Date_Time column however it says Binary and when i try to convert it to a date time format it says error.

I am basically trying to get the end time into the same line as the start time and then delete all the rows with 0 (the end time) from the data set.

Can anyone help.

 

[sandy666]

if [STAT] = 0 then [Date_Time] else null

= Table.AddColumn(Source, "IF", each if [STAT] = 0 then [Date_Time] else null)

 

[th081]

this is what i have at the moment:

= Table.AddColumn(#"Renamed Columns1", "Custom.1", each if[STAT]= 0 then [DATE_TIME] else null)

if i change to: = Table.AddColumn(#"Renamed Columns1", "Custom.1", "IF", each if[STAT]= 0 then [DATE_TIME] else null)

it throws an error

Expression.Error: We cannot convert the value "IF" to type Function.
Details:
Value=IF
Type=Type

 

[sandy666]

of course it's error
wrong = Table.AddColumn(#"Renamed Columns1", "Custom.1", "IF", each if[STAT]= 0 then [DATE_TIME] else null)

= Table.AddColumn(#"Renamed Columns1", "IF", each if [STAT] = 0 then [DATE_TIME] else null)

use Add Column - Conditional Column then