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

     

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....
    • Einomi's avatar
      Einomi
      Helper V
      3 years ago

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

      • artemus's avatar
        artemus
        Microsoft Employee
        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