Forum Discussion
Use power query to add a column
Hi,
I have a series of tables run through an external company using a server and the data I receive is all in direct query mode. I hope thats enough information for you to understand the set up.
I have the below table in the power query editor and I would like to add a column to this table so I can filter the 'start_date' field to values which are only this week (Monday to Sunday). I only want a count visualisation which I will then use microsoft flow to email our suppliers. I found the following code - 'Date.IsInCurrentWeek(DateTime.FixedLocalNow())' but I'm not sure how to join this with the current below code to create a column in the 'client_dimension' table
= Source{[Schema="dw",Item="client_dimension"]}[Data]
I hope you can help.
Thanks.
Hi Anonymous
As tested, Date.IsInCurrentWeek works on my side when connecting to sql server in direct query mode.
Besides methods provided above, you could create measures to get desired visual.
New measure->
Measure = CALCULATE(COUNT('Table'[value]),FILTER('Table',WEEKNUM([date],2)=WEEKNUM(TODAY(),2)))Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous
You can use a relative date dimension as per blog post below.
https://community.powerbi.com/t5/Community-Blog/Relative-Date-Dimension/ba-p/779039
Or if you want to add a custom column to your table ( which I don't think you can in Direct Query mode ) then the code would be something like.
Date.IsInCurrentWeek( [Your Date Column] )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedInHi Mariusz
Thanks for taking the time to reply to my query. I have tried to add a column and as I thought, the limited dax formula options in direct query mode means that it doesn't work.
I've read the blog that you shared and I've tried to add the code written into a blank query in power query but it gave an error.
Thanks,
Hello Anonymous
you don't need to add a new column. Just use this syntax instead (not tested)
=Table.SelectRows(Source{[Schema="dw",Item="client_dimension"]}[Data], each Date.IsInCurrentWeek([start_date]))
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi Jimmy801 ,
Unfortunately the syntax
IsInCurrentWeek
doesn't work as i'm in direct query mode.
Thanks,
Hello Anonymous
does this work?
=Table.SelectRows(Source{[Schema="dw",Item="client_dimension"]}[Data], each Date.WeekOfYear([start_date])= Date.WeekOfYear(DateTime.FixedLocalNow())If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi Anonymous
As tested, Date.IsInCurrentWeek works on my side when connecting to sql server in direct query mode.
Besides methods provided above, you could create measures to get desired visual.
New measure->
Measure = CALCULATE(COUNT('Table'[value]),FILTER('Table',WEEKNUM([date],2)=WEEKNUM(TODAY(),2)))Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
