Power Community

Power Community

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

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.

Bennykil_0-1705709698323.png

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

Bennykil_1-1705709699757.png

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))

Bennykil_2-1705709699134.png

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.

Bennykil_3-1705709699363.png


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())

Bennykil_4-1705709698328.png

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]))

Bennykil_5-1705709698462.png

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.

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Clone Dynamics 365 CRM Sales Order Records along with their Related Invoice Records!

Sales teams using Dynamics 365 CRM have to frequently create new sales orders and invoices based on existing ones....

More Articles Like This

- Advertisement -spot_img