Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe 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.
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!
Solved! Go to Solution.
1) Select the date column in Power Query, select add a new column, and select the Date options in the ribbon:
You will get this
2) Now select the day column and filter out the days you don't need.
Proud to be a Super User!
Paul on Linkedin.
hello, i tried seeing previous solution, but i couldn't as the Date option is inactive. please help find the solution
got it, thanks
1) Select the date column in Power Query, select add a new column, and select the Date options in the ribbon:
You will get this
2) Now select the day column and filter out the days you don't need.
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
Best regards
@Anonymous
Try changing the Fecha.Fallo column to type Date (not Date/time) before adding the Day column
Proud to be a Super User!
Paul on Linkedin.
I knew it was something easy I just hadn't considered..THANK YOU!
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!
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |