Forum Discussion

Einomi's avatar
Einomi
Helper V
3 years ago
Solved

Declare a variable in a step

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", ...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • artemus's avatar
    artemus
    3 years ago

    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

     

Einomi's avatar
Einomi
Helper V
3 years ago

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

 

  • artemus's avatar
    artemus
    Microsoft Employee
    3 years ago

    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. 

    • Einomi's avatar
      Einomi
      Helper V
      3 years ago

      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 🙂 😉

      • artemus's avatar
        artemus
        Microsoft Employee
        3 years ago

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