Variables in if statement

[iainh]

Hi all

Firstly thank you for taking time out of your day to help me learn. I'm coming from a Excel (VBA) background like most new people to PBI / PQ I think.

I'm looking at Project dates and I've created a error type column where the project tasks don't comply to logic or business processes. I've got the if statements to work individually but stop on the first occurrence. What I would like to do (based on my VBA experience) is put the True occurrence's into a variable and report the variable in the newly added column, not the first occurrence. Example of current code:

#"Added custom" = Table.AddColumn(#"Changed column type", "Error Checking", each
if [Task Actual Start Date] <> null and [Task Actual Start Date] > Date.From(DateTime.LocalNow()) then "Actual Start is a future date" else 
if ([Task Actual Finish Date] <> null and [Task Actual Start Date] <> null) and [Task Actual Finish Date] < [Task Actual Start Date] then "Task Ends before it begins" else
null)  

But where the [Task Actual Start Date] is a future date and where the [Task Actual Finish Date] is before the [Task Actual Start Date] I would like to report both with something like "Actual Start is a future date AND Task Ends before it begins". I know I can multiple new columns but the query is already 100+ columns big over 100k entries and to be honest, I didn't know if that was my mind just been lazy and shying away from an opportunity to learn. I've tried to look online for ideas on how variables in PQ work but nothing is clicking (so to speak) in my head. 

Again, thanks for any advice / support you can give 

Iain

[jbwtp · Accepted solution]

Hi @iainh,

I know what you mean :). M is a "flow" language, which is quite different to VBA, which is "transactional".

Therefore, you are programming transitions rather than states  as in a more traditional languages.

I think, this is quite important to bear in mind this (seemgly simple) difference. For me, comming from VB + C# this was quite a change in paradigm.

In your case, this is enough to see each conditions as a separate function (kind of a delegate in C# if you wish). Therefore, it is possible to record it as return "Actual Start is a future date" if condition1 is met, combine with return "Task Ends before it begins" if condition 2 is met:

#"Added custom" = 
    Table.AddColumn(#"Changed column type", "Error Checking", 
        each
            Text.Combine(
                {
                    if [Task Actual Start Date] <> null and [Task Actual Start Date] > Date.From(DateTime.LocalNow()) then "Actual Start is a future date" else "",
                    if ([Task Actual Finish Date] <> null and [Task Actual Start Date] <> null) and [Task Actual Finish Date] < [Task Actual Start Date] then "Task Ends before it begins" else ""
                },
                " AND "
    ) 

I do not test the above, but I guess even if this does not work straight from the source, you will be able to see the idea and tweak it to make it work.

Cheers,

John

[iainh]

Hi John 

Your right. I've never consider what type of language M is before. I've only seen it as the natrual succesor (for my purposes at least!) to VBA where transforming data sets for reporting.  Thank you for the suggestion, and to other that have viewed this post. I'll have a go at using the code today and see if I can expand my knowledge somewhat!

Thanks again 

Iain

[AntrikshSharma]

@iainh Can you prepare a sample Excel file with those 2 columns and the result column, share it using google/one drive.