Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
rachelbabcock
Helper I
Helper I

How to pull Day from Date in Power Query Editor

Hi! I have a question that should hopefully be simple to resolve.

 

In my dataset, I have a "dates" column that includes every single day between January 1 and December 31. I need to narrow this to just include rows where the day of the month is the 1st. I'd like to do this in Power Query Editor so that I can limit the size of data being pulled into my report, but cannot figure out how to write this using M language.

 

Esesntially, I just need a custom column that says IF(Day(DatesColumn) = 1, 1), because then I'll filter my dataset to just the 1s. 

 

Any help would be great! Thanks!

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@rachelbabcock 

1) Select the date column in Power Query, select add a new column, and select the Date options in the ribbon:

datePQ.png

 

You will get this

Captura.JPG

 

2) Now select the day column and filter out the days you don't need.

 

DatePQ filter.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

7 REPLIES 7
russell_rsd
New Member

russell_rsd_0-1645524858500.png

hello, i tried seeing previous solution, but i couldn't as the Date option is inactive. please help find the solution

got it, thanks

PaulDBrown
Community Champion
Community Champion

@rachelbabcock 

1) Select the date column in Power Query, select add a new column, and select the Date options in the ribbon:

datePQ.png

 

You will get this

Captura.JPG

 

2) Now select the day column and filter out the days you don't need.

 

DatePQ filter.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hello!

I am trying your solution but when I extract the day, it gives me one less number, that is, the date 28/11/22, it gives me day 27

Cristinapc_0-1671103605011.png

Best regards

@Anonymous 

Try changing the Fecha.Fallo column to type Date (not Date/time) before adding the Day column





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I knew it was something easy I just hadn't considered..THANK YOU!

VijayP
Super User
Super User

@rachelbabcock 

in Power Query you can use Add Column from Example . Right Click on Date Column and use Add column from Example which will invoke a Blank Column in the Extreme Right. Change the column name what ever you want and inthe First Row Type 1 (assuming the Date in that row is 1st ) and click OK to get only date number 1,2,3 etc . 

Let me know if this helps or need any further help!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.