convert string to date

Question

Hello,
In dataflow source, I receive the postdate column as string i.e.

20220530
20210422
20220605
...

In the filter transformation, how can I filter only for the postdate in the previous month?
for the above example I should get data for:
20220530
perhaps something like: between 1st of previous month and last day of previous month?

Thank you

Answer 1

Hi @arkiboys ,
Thankyou for using Microsoft Q&A platform and thanks for posting your question.

As per my understanding about your query, you are trying to filter out previous month dates using Mapping dataflow. Please let me know if my understanding is incorrect.

Here , we need to check two things - Year of the datecolumn should be the same year as Current Year and month should be current month -1 . I am using same dates provided by you in the query as source column value.

• Use this expression in the first filter transformation to make sure year belongs to current Year: equals(year(toDate(postdate,'yyyyMMdd')),year(currentUTC()))

• Use the following expression in next filter transformation to check the month belongs to the immediate previous month: equals(month(toDate(postdate,'yyyyMMdd')), month(currentUTC())-1)

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hello @arkiboys

Have a look at this expression if you would like to try one more way.

substring(dtcol,1,4)==toString(year(currentDate("yyyy-MM-dd"))) && lpad(toString (month(currentDate("yyyy-MM-dd"))-1),2,'0')

Lpad is done to manage the months getting converted without preceding 0 like 5,6 etc

Please let us know for issues.

Thanks.