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.
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?
Solved! Go to Solution.
Hi @benjamin_sasin ,
Yep, it is only syntax problem, please try instead:
if [GRADYYYYMM] = null or Text.Length([GRADYYYYMM]) < 6 then null else Date.FromText(Text.Combine({Text.Start([GRADYYYYMM],4),Text.End([GRADYYYYMM],2),"01"},"-"))
IF SYNTAX in M Language:
if <test> then <output1> else <output2>
NESTED SYNTAX (just append the if after else and everything is the same):
if <test> then <output1> else if <test2> then <output2> else if <test3> then <output3> else <output4>
Any question, just let me know,
Regards,
Gian Carlo Poggi
Hi @benjamin_sasin ,
Yep, it is only syntax problem, please try instead:
if [GRADYYYYMM] = null or Text.Length([GRADYYYYMM]) < 6 then null else Date.FromText(Text.Combine({Text.Start([GRADYYYYMM],4),Text.End([GRADYYYYMM],2),"01"},"-"))
IF SYNTAX in M Language:
if <test> then <output1> else <output2>
NESTED SYNTAX (just append the if after else and everything is the same):
if <test> then <output1> else if <test2> then <output2> else if <test3> then <output3> else <output4>
Any question, just let me know,
Regards,
Gian Carlo Poggi
I forgot the "then"--duh (facepalm)! Thanks a lot!
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.