Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have the following code to add the ISO week number based on my column date, which is called DateProduced
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No",
each if
Number.RoundDown((Date.DayOfYear([DateProduced])-(Date.DayOfWeek([DateProduced], Day.Sunday)+1)+10)/7)=0
then
Number.RoundDown((Date.DayOfYear(#date(Date.Year([DateProduced])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([DateProduced])-1,12,31), Day.Sunday)+1)+10)/7)
else if
(Number.RoundDown((Date.DayOfYear([DateProduced])-(Date.DayOfWeek([DateProduced], Day.Sunday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([DateProduced]),12,31), Day.Sunday)+1<4))
then
1
else
Number.RoundDown((Date.DayOfYear([DateProduced])-(Date.DayOfWeek([DateProduced], Day.Sunday)+1)+10)/7)),
I use this step into many queries and I would like to add a variable INSIDE this step to be able to update the column date, in this query my dates are sitting in the column DateProduced, but in other queries they are sitting in different columns names such as OrderDate, Date etc...
Anyone can help ?
Thanks for your time
Solved! Go to Solution.
oh sorry about that the date at the end should be removed:
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No", each
let date = [DateProduced] in
if
Number.RoundDown((Date.DayOfYear(date)-(Date.DayOfWeek(date, Day.Sunday)+1)+10)/7)=0
then
Number.RoundDown((Date.DayOfYear(#date(Date.Year(date)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(date)-1,12,31), Day.Sunday)+1)+10)/7)
else if
etc....
in fact you should be able to just add this almost anywhere:
let myVar = SomeCalculation in
Thanks @artemus
However, I am not sure where to put it in my M code above
Does it work if I do like below ?
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No",
let
date = [DateProduced]
in
date
each if
Number.RoundDown((Date.DayOfYear(date)-(Date.DayOfWeek(date, Day.Sunday)+1)+10)/7)=0
then
Number.RoundDown((Date.DayOfYear(#date(Date.Year(date)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(date)-1,12,31), Day.Sunday)+1)+10)/7)
else if
etc....
The problem with your example is that you have the each after the let. Any code before each doesn't have access to the current row. In short, a let before the each would make any values you declare the same for every row. If after the each, then it can access the row data.
. You can put the let in anaywhere you would put a number or a string, or similar value.
@artemus I am sorry I am still confused, any chance you could help with writing the code, look like you know very good M code 🙂 😉
From your example, you would move the each up to just before your let statement like:
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No", each
let
date = [DateProduced]
in
date
if
Number.RoundDown((Date.DayOfYear(date)-(Date.DayOfWeek(date, Day.Sunday)+1)+10)/7)=0
then
Number.RoundDown((Date.DayOfYear(#date(Date.Year(date)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(date)-1,12,31), Day.Sunday)+1)+10)/7)
else if
etc....
Hi @artemus
Thanks for your reply.
However, my query editor indicated me that we need a comma like below
let
mydate = [DateProduced]
in
mydate,
etc...
From here there is no more syntax erros, however, in the UI, I got an error saying that PQ cannot find mydate...
oh sorry about that the date at the end should be removed:
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No", each
let date = [DateProduced] in
if
Number.RoundDown((Date.DayOfYear(date)-(Date.DayOfWeek(date, Day.Sunday)+1)+10)/7)=0
then
Number.RoundDown((Date.DayOfYear(#date(Date.Year(date)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(date)-1,12,31), Day.Sunday)+1)+10)/7)
else if
etc....
in fact you should be able to just add this almost anywhere:
let myVar = SomeCalculation in
you can use let .. in anywhere e.g.
Table.AddColumn(Source, each
let
sum = [A] + [B],
calc = (sum - [A]) * (sum - [B])
in
calc)