cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

PBI Power Query M Code ---- Filter 1st Date Capture of the Month

Hello All, Thanks in advance for your help and time.

 

I need to capture 1st Ocurrance only of Data with the DATE in a Month, I had a filter for less than 2nd Week of Month and less than 6th Day of month, to be able to get to that initial Snapshot in case there was a second close by entry.

 

The issue I have now, the entry was done on day 10, week 3. 

So I need to adjust my Filter to Capture the 1st Instance of Date in the Data by Month.

Any Ideas?

 

 
 

date filter.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: PBI Power Query M Code ---- Filter 1st Date Capture of the Month

Hi @Joorge_C

 

I made a sample table as below:

Annotation 2020-06-02 095017.png

Then take the steps as below:

In query editor>add column>custom column,put the below M code inside:

Annotation 2020-06-02 095158.png

 

Then right click on the column "Month", choose "group by "funtion and make the selection as below:

Annotation 2020-06-02 095400.pngAnnotation 2020-06-02 095526.png

 

Finally you will see:

Annotation 2020-06-02 095731.png

For the related .pbix file,pls click here.(You can also check the details in "advanced editor")

 

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: PBI Power Query M Code ---- Filter 1st Date Capture of the Month

Hi @Joorge_C

 

If I didnt understand wrong that you wanna create a slicer and show the 1st date of the Month,right?

If so,create a calculated column as below:

 

Column = MINX(FILTER('Table',MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),'Table'[Date])

 

Finally you will see:

Annotation 2020-06-01 112249.png

For the related .pbix file, pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Highlighted
Resolver I
Resolver I

Re: PBI Power Query M Code ---- Filter 1st Date Capture of the Month

Hi @v-kelly-msft , Thanks for your inputs.

No Filters. This is on the M Code on the Import of Data

 

This is in the import of the data in power query, there is a daily snapshot in the data and I need to grab the first occurance of date in the month by month and filter out the rest of the Dates. The data is huge so this needs to be done in Query to the PBI is not impacted by having data in teh back end that is not needed.

Highlighted
Community Support
Community Support

Re: PBI Power Query M Code ---- Filter 1st Date Capture of the Month

Hi @Joorge_C

 

I made a sample table as below:

Annotation 2020-06-02 095017.png

Then take the steps as below:

In query editor>add column>custom column,put the below M code inside:

Annotation 2020-06-02 095158.png

 

Then right click on the column "Month", choose "group by "funtion and make the selection as below:

Annotation 2020-06-02 095400.pngAnnotation 2020-06-02 095526.png

 

Finally you will see:

Annotation 2020-06-02 095731.png

For the related .pbix file,pls click here.(You can also check the details in "advanced editor")

 

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors