Showing results for 
Search instead for 
Did you mean: 

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


You can find the sample dataset on GitHub:


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


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




15. Click OK.


Problem solved.


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.

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: and twitter I also write at, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – Youtube channel -