cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Strickleyt Frequent Visitor
Frequent Visitor

Syntax Conflict between Advanced Editor and Custom Column Builder

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

Accepted Solutions
Strickleyt Frequent Visitor
Frequent Visitor

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

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. 

12 REPLIES 12
SPG Member
Member

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

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.

Strickleyt Frequent Visitor
Frequent Visitor

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

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

Strickleyt Frequent Visitor
Frequent Visitor

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

Area.JPGMain Window where trying picture

Super User
Super User

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
SPG Member
Member

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

@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

Strickleyt Frequent Visitor
Frequent Visitor

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

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 Smiley Happy

Super User
Super User

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

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 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Strickleyt Frequent Visitor
Frequent Visitor

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

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. 

Super User
Super User

Re: Syntax Conflict between Advanced Editor and Custom Column Builder

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!