Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
CaveOfWonders
Helper IV
Helper IV

M Query Syntax help

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?

 

StartofWeek.JPG

6 REPLIES 6
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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"

 

 

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

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):

Result.png

 

 

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

Mark it as Solution if you find it usefull.

Hennadii
Helper IV
Helper IV

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.