Modifying M-code in Power Query in Power Automate to send mail that only shows data from the weekday

[Bennykil]

Today, we will modify the M code in Power Query to get the last working date value in our final table.

I found this while working with a colleague and was faced with a sticky situation at work. I have this table in my database. However, I would like to send the data from the period date a day late. For example, I would like an email reporting Monday data on a Tuesday morning. So Friday reports Thursday data.

Now, here’s the problem. I would like to report Friday data, not Sunday data on Monday. 

How did we solve it? Let's find out

Dataset

You can find the sample dataset on GitHub: https://github.com/Bennykillua/Project/tree/main/PowerAutomate_Sales_Date

The SQL script to create and insert the data is in the ReadMe file.

Creating The Flow

1. First, we will create an automated cloud flow or manually trigger flow.

2. Once done, pick the transform data using Power Query.

3. Pick the table from the database after filling in your credentials.

Now, let us get to it.

4. Since we want to see the previous date, we will use the filter in the date column to filter the date just for the previous date.

However, this just solves the first half of the problem.

 
5. Copy the M code and write it down in a note.

Table.SelectRows(#"Changed column type", each Date.IsInPreviousNDays([SalesDate], 1))

6. Undo that step.

7. Next, we will use the conditional format so we can get the IF statement  syntax in the M code.

8. Click on Add Column => Conditional Column.

9. Since we just need the syntax, you can use any column. For example, we can use the product column to say if the product is an instant noodle, it should give us Food else Snacks.

10. Just like step 5, copy the M code generated, write it down in a note, and undo that step.

Table.AddColumn(#"Changed column type", "Test", each if [Product] = "Instant noodles" then "Food" else "Snacks")

11. Click on the Advanced Editor to edit our M code with the code we copied earlier.

12. First we need to get the day of the week. We can do that with the syntax below.

CurrentDayName = Date.DayOfWeekName(DateTimeZone.UtcNow())

13. Now that we have the date of the week, lets use an IF statement to ask for Friday value if the date of the week is Monday, other it should stick to the previous day value.

14. Back on the Advanced Editor add the syntax below.

#"Filtered rows" = 

        if CurrentDayName = "Monday" then

            Table.SelectRows(#"Changed column type", each Date.IsInPreviousNDays([SalesDate], 3))

        else

            Table.SelectRows(#"Changed column type", each Date.IsInPreviousDay([SalesDate]))

15. Click OK.

Problem solved.

Conclusion

Like I mentioned there are various ways to accomplish or solve the problem. In this article, we did the processing with the M-code. How would you solve this if you have a different workaround?

I hope you found this blog useful, and as always. Also this is pretty much my first article on the community blog, feedback is very much welcome.

[Bennykil]

Dataset: https://github.com/Bennykillua/Project/tree/main/PowerAutomate_Sales_Date.