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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Strickleyt
Frequent Visitor

Syntax Conflict between Advanced Editor and Custom Column Builder

Screenshot of Error and ConflictScreenshot of Error and Conflict

Please look at this snapshot which shows my code.  I don't understand what the conflict is for the Express.Error presented when it clearly shows 'no syntax errors have been detected'.  Can someone help me?  This code works fine when I use in the Presentation Layer and create a calculated column to determine my last weekend.  I use TODAY or NOW and both work fine there.  I appreciate the assist, I have searched forums and cannot find any help Smiley Sad

1 ACCEPTED SOLUTION

With no disrespect, I hesitate to say this is resolved as your answer only points out the obvious which is incredibly frustrating to a user looking for a real answer that doesn't involve learning yet one more language and the 'custom column' builder should have taken care of this or give me a message 'DOESNT WORK NEED POWER QUERY M SYNTAX- if this were written obviously somewhere then I would take it as a known universal answer and would have never tried the approach provided in Power BI.  These nuances are not straight forward and I know I am not the only person having issues with these 'nuances' that are just horrible.  It's like throwing spaghetti at a wall constantly to see what sticks.   Users shouldn't have to do that. 

View solution in original post

12 REPLIES 12
TomMartens
Super User
Super User

Hey,

 

so the custom column builder checks if the syntax is correct, meaning did forget a comma, a required parameter, braces and similar things, but unfortunately it does not check if the functions you are using are existing in the Power Query environment, meaning M functions. now() is not a M function - the language of Power Query, but "just" a DAX function.


An essential part is missing from you screenshot, see the arrow from the screenshot below, it shows that are in Power Query Editor:

image.png

 

 

 

Here you will find the reference of all the M functions

https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-reference

e.g. you have to use DateTime.LocalNow in M

https://msdn.microsoft.com/en-us/query-bi/m/datetime-localnow

and can use NOW() in DAX. Please be aware that M is case sensitive 

 

Maybe this gets you started using DAX to create "calculated columns":

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns


Baiscally you have to decide when to M or DAX, sometimes you can create a column inside a table in both environments. As always it depends.

Regards,
Tom

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

The "No syntax errors" message only checks that there are no missing commas or parenthesis or something like that, but it doesn't check if the functions you are writing actually exist.

 

The other error (function not recognised) is because you are writing a DAX formula in Power Query, which uses M language.

Try creating the column in Power BI Desktop main window.

thanks for trying to assist, the now and today functions are valid.  Can you please clarify where this window is?  I am in the main window.  "Try creating the column in Power BI Desktop main window."

Main Window where trying pictureMain Window where trying picture

Anonymous
Not applicable

@Strickleyt: that isn't the main window, that is the Query Editor.

This is the main window:

PBI Desktop.PNG

 

And here you can creat a new column and write your DAX formula:

 

New column.PNG

Yes, that is the presentation layer to me.  And I don't want to do the transformation there because I have to create dynamic groupings and it just gets crazy with slicer/Drillthroughs to me anyway, if you don't try to do as much data modeling as you can in the Query Editor Window.

 

Thank you 🙂

So, now that this is clarified, and we have mentioned that you can't use DAX functions in Power Query. Is your question answered?

 

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

With no disrespect, I hesitate to say this is resolved as your answer only points out the obvious which is incredibly frustrating to a user looking for a real answer that doesn't involve learning yet one more language and the 'custom column' builder should have taken care of this or give me a message 'DOESNT WORK NEED POWER QUERY M SYNTAX- if this were written obviously somewhere then I would take it as a known universal answer and would have never tried the approach provided in Power BI.  These nuances are not straight forward and I know I am not the only person having issues with these 'nuances' that are just horrible.  It's like throwing spaghetti at a wall constantly to see what sticks.   Users shouldn't have to do that. 

So, as I esperince the people here in this forum I guess you will get a quick solution, if you provide a pbix file that contains sample data, upload the file to onedrive or dropbox and share the link.

 

Please describe, what days mark a weeken, e.g Saturday and Sunday.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Have the file ready to share, here is what I am trying to do and I appreciate the help I really do I have been driving myself crazy for WEEKS trying to figure this out

 

1.  Create dynamic grouping and filtering on the dataset called Performance_All_Dept

2.  The heirarchy (Region, Market, Store) is flat in this table.  I need to dynamically group by Heirarchy and sum the data in the CY and PY columns to then create  a YOY % of that Agg'd data by group selected.  I don't want to create slicers, rather the data be the slicer and Weekend data and MTD data on separate tabs OR

3.  My slicers need to be by Last Weekend from today (my code here works in DAX beautifully in the 'main window' but I want to build it in the query editor to eliminate all the other stuff done in the 'main window' (to me this is the presentation layer) 

 

IsWeekend = IF(Performance_ALL_Dept[MonthDate]= TODAY()-WEEKDAY(TODAY())+1|| Performance_ALL_Dept[MonthDate] = TODAY()-WEEKDAY(TODAY())|| Performance_ALL_Dept[MonthDate] = TODAY()-WEEKDAY(TODAY())-1,"Weekend","")

 

 

4.  My slicer also needs to slice by MTD

 

https://drive.google.com/drive/folders/1G4LvM8r1FNLxjtg8lNxMZYauwhWwqwYN?usp=sharing

Hey,

 

unfrortunately I can't download a file from googledrive.

 

But nevertheless, you can use this formula to create a custom column in the Query Editor to determine if a given date is a weekend

if List.Contains({5,6},Date.DayOfWeek([Date], Day.Monday)) then "is weekend" else "no weekend"

[Date] references the date column in your table

The function Date.DayOfWeek returns values from 0 to 6, 0 is the starting day of the week. The starting day of a week is set by the 2nd parameter Day.Monday. This means that Saturday and Sunday are represented by the values 5 and 6.

The formula checks if the returned value is in the list {Day.Saturday, Day.Sunday} if true then it's a weekend otherwise not.

 

For the days of september the function returns the values below (assuming the week starts with a Monday.

image.png

 

This formula flags the most recent weekend

if
List.Contains({4,5},
Duration.Days(
#date(
Date.Year(DateTime.LocalNow())
,Date.Month(DateTime.LocalNow()),
Date.Day(DateTime.LocalNow())) - [Date])) 
then "yes" else "no"

Here is a little screenshot:

image.png

 

Please be aware that the calculation will be just executed if the table will be refreshed.

 

Hopefully this gets you started

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom, I really appreciate the assist!  Share with mailer@lithium.com or?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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