if statement in a new column not working

Question

New to Power Query M here, I'm converting a source column's YYYYMM to date, which works well, except it produces an error whenever the source column is null.

I'm trying to wrap an IF statement in that to cope with the error (BTW can't find any reference to IF statements in the documentation?)

Table.AddColumn(#"Changed Type", "GRADYEARMONTH", each   Date.FromText(Text.Combine({Text.Start([GRADYYYYMM],4),Text.End([GRADYYYYMM],2),"01"},"-")))

Becomes:

Table.AddColumn(#"Changed Type", "GRADYEARMONTH", each if Text.Length([GRADYYYMM])<6 null else  Date.FromText(Text.Combine({Text.Start([GRADYYYYMM],4),Text.End([GRADYYYYMM],2),"01"},"-")))

But it won't take that. Surely I messed up something, but what?

gpoggi · Accepted Answer

Hi&nbsp;benjamin_sasin&nbsp;,&nbsp;Yep, it is only syntax problem, please try instead:&nbsp;&nbsp;if
  [GRADYYYYMM] = null or Text.Length([GRADYYYYMM]) &lt; 6
then
  null
else
  Date.FromText(Text.Combine({Text.Start([GRADYYYYMM],4),Text.End([GRADYYYYMM],2),"01"},"-"))IF SYNTAX in M Language:&nbsp;if &lt;test&gt; then &lt;output1&gt; else &lt;output2&gt;&nbsp;&nbsp;NESTED SYNTAX (just append the if after else and everything is the same):&nbsp;if &lt;test&gt; then &lt;output1&gt; else if &lt;test2&gt; then &lt;output2&gt; else&nbsp;if &lt;test3&gt; then &lt;output3&gt; else &lt;output4&gt;&nbsp;Any question, just let me know,&nbsp;Regards,&nbsp;Gian Carlo Poggi&nbsp;&nbsp;

Forum Discussion

if statement in a new column not working

2 Replies

Related content

New Column with IF STATEMENT

IF statement in custom column

OR Statement ?

Switch Statement Not Working

subtotal not work in metrics (value has "if" statement)