M Query Syntax help

[CaveOfWonders]

I found the M code below online, but cannot get it to work. The code is supposed to allow you to get a week start date calculation

I'm getting the error:

An error occurred in the ‘’ query. Expression.Error: The name 'Week Start' wasn't recognized. Make sure it's spelled correctly.

Could someone please help me get this to work, I know it's probably something quite easy.

What do I need to change/add to get this to work?

[mahoneypat]

The error is the reference to your previous step - #"Week Start".  You need to reference your previous step.  In your query, on the Add Column tab, add Custom Column and put the Date.ToText( ....) part in the box that comes up.  Also make sure the column with date values is called [Date], otherwise change that too.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[CaveOfWonders]

Thank you for your reply Pat; I've added the code here to make it easier to edit as I haven't been able to get it to work sorry. There are no previous steps, this is a standalone column? Add Custom column wasn't coming up as an option? Sorry 🤔

let
Source = Table.AddColumn(#"Week Start", each Date.ToText(Date.StartOfWeek([Date], Day.Sunday),"MM/dd/yyyy"))
in
#"Week Start"

[Hennadii]

@CaveOfWonders , you mentioned you found this code somwhere online. Do you have a link?

[CaveOfWonders]

Sure @Hennadii, the link is here:

https://community.powerbi.com/t5/Desktop/Week-Start-calculation/td-p/433890

[Hennadii]

Hey @CaveOfWonders ,

So, you are trying to build a calendar using query and have there columns there with start date and end date of weeks.

The post you mentioned has only a part of that query and looks incomplete.

Here is entire querry to build a calendar for year. At the end of that query you'll find  three columns with mentioned dates: Start Date, End Date, Start - End Date.

let

    Source = List.Dates,
    #"Invoke function" = Source(#date(2020, 01, 01), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())- #date(2019, 01, 01))+1, #duration(1, 0, 0, 0)),
  
    #"List to table" = Table.FromList(#"Invoke function", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    Date = Table.RenameColumns(#"List to table",{{"Column1", "Date"}}),
 
    #"Day Added" = Table.AddColumn(Date, "DayNo", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
  
    #"Day Name Added" = Table.AddColumn(#"Day Added", "Day Name", each Date.ToText([Date],"ddd","en-US")),
    #"Month No Added" = Table.AddColumn(#"Day Name Added", "Month No", each Date.Month([Date])),

    #"Month Name Added" = Table.AddColumn(#"Month No Added", "Month Name", each Date.ToText([Date],"MMM","en-us")),

    #"Quarter No Added" = Table.AddColumn(#"Month Name Added", "Quarter No", each Date.QuarterOfYear([Date])),

    #"EUROPEAN Week No added" = Table.AddColumn(#"Quarter No Added", "WeekNo", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])+1),2,"0")),

    #"Year Added" = Table.AddColumn(#"EUROPEAN Week No added", "Year", each Date.Year([Date])),

    #"Year Month Added" = Table.AddColumn(#"Year Added", "Year-Month", each Number.ToText([Year])&"-"&[Month Name]),
    #"Year Week Added" = Table.AddColumn(#"Year Month Added", "Year-Week", each Number.ToText([Year])&"-"&[WeekNo]),

    #"Year Quarter Added" = Table.AddColumn(#"Year Week Added", "Year-Quarter", each Number.ToText([Year]) & "Q"& Number.ToText([Quarter No],"00")),

    #"Change type to text" = Table.TransformColumnTypes(#"Year Quarter Added",{{"Year", Int64.Type}, {"Date", type date}, {"Month No", Int64.Type}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter No", Int64.Type}, {"Year-Quarter", type text}, {"Year-Month", type text}, {"WeekNo", Int64.Type}, {"DayNo", type text}, {"Year-Week", type text}}),

    #"Sort DayName" = Table.AddColumn(#"Change type to text", "SortDayName", each Date.DayOfWeek([Date],1)),

    #"Sort YearMonth" = Table.AddColumn(#"Sort DayName", "SortYearMonth", each Number.ToText([Year])&Text.PadStart(Number.ToText([Month No]),2,"0")),
    #"Sort YearWeek" = Table.AddColumn(#"Sort YearMonth", "Sort YearWeek", each Number.ToText([Year])&Text.PadStart(Number.ToText([WeekNo]),2,"0")),

    #"Sort YearQuarter" = Table.AddColumn(#"Sort YearWeek", "SortYearQuarter", each Number.ToText([Year])&Text.PadStart(Number.ToText([Quarter No]),2,"0")),

    #"Changed Type to NO" = Table.TransformColumnTypes(#"Sort YearQuarter",{{"SortYearMonth", Int64.Type}, {"SortYearQuarter", Int64.Type}, {"SortDayName", Int64.Type}, {"Sort YearWeek", Int64.Type}, {"Year-Week", type text}}),

    #"Add Week Start Date" = Table.AddColumn(#"Changed Type to NO", "WeekStartDate", each Date.ToText( Date.StartOfWeek([Date], Day.Sunday), "MM/dd/yyyy")),

    #"Add Week End Date" = Table.AddColumn(#"Add Week Start Date", "WeekEndDate", each Date.ToText( Date.EndOfWeek([Date], Day.Sunday), "MM/dd/yyyy")),

    #"Add Week Start - End Date" = Table.AddColumn(#"Add Week End Date", "WeekStart_EndDate", each
        Date.ToText( Date.StartOfWeek([Date], Day.Sunday), "MM/dd/yyyy")
        & "-" &
        Date.ToText( Date.EndOfWeek([Date], Day.Sunday), "MM/dd/yyyy")
        )

in
    #"Add Week Start - End Date"

In the result, your table will look like this (I removed some columns for simplicity):

Hope it helps you to understand a part of a query you posted.

Mark it as Solution if you find it usefull.

[Hennadii]

Hi @CaveOfWonders 

It looks like you miss definition what is #"Week Start", equal sign is useless before let, and anfter in, I suppose should be Source as a return value.

Please refer to the link below to learn more about let/in syntax:

https://docs.microsoft.com/en-us/powerquery-m/expressions-values-and-let-expression