Use power query to add a column

[Anonymous]

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.

[v-juanli-msft · Accepted solution]

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.

[Jimmy801]

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

Jimmy

[Anonymous]

Hi @Jimmy801 ,

Unfortunately the syntax 

IsInCurrentWeek

 doesn't work as i'm in direct query mode.

Thanks,

[Jimmy801]

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

[Mariusz]

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.
LinkedIn

[Anonymous]

Hi @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,

[Mariusz]

Hi @Anonymous 

The article has a pbix file with an example, you can download it, copy the structure to your model and later use it as a date dimension.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn