Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
58 |