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
fiwright1980
New Member

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field]..

This is a simple Power Query but as I'm new to using Power BI i'm completely stuck. Any help would be greatly appreciated.

 

 - "Assessment Status" is a column with either "Required" or "Not Required" text in it.

Then 3 separate columns "Induction Formula Expiry Date", "Subs Formula Date" and "Card Expiry Date" which either contain a date or are blank/empty.

If "Assessment Status" contains Not Required then it returns Not Required. 

If "Assessment Status" contains Required then all the dates in the "Induction Formula Expiry Date", "Subs Formula Date" and "Card Expiry Date" columns need to be today's date or in the future.

If all dates are today or in the future then the text "Completed" is returned.  If one or more of those dates is in the past (yesterday or before) then it returns the text "Incomplete".

I have got as far as the code below. Please Help!  Thanks so much in advance.

 

= Table.AddColumn(#"Added Custom", "Completion", if [#"Assessment Status"] = "Not Required" then "Not Required" else if [#"Induction Formula Expiry Date"] >= [#"Today's Date"] and [#"Subs Formula Date"] >= [#"Today's Date"] and [#"Card Expiry Formula Date"] >= [#"Today's Date"] then "Completed" else "Incomplete")

3 REPLIES 3
edhans
Super User
Super User

You need the word "each" in all lower case before the keyword "if" in that function.

 

 

= Table.AddColumn(#"Added Custom", "Completion", each if [#"Assessment Status"] = "Not Required" then "Not Required" else if [#"Induction Formula Expiry Date"] >= [#"Today's Date"] and [#"Subs Formula Date"] >= [#"Today's Date"] and [#"Card Expiry Formula Date"] >= [#"Today's Date"] then "Completed" else "Incomplete")

 

If that fixes it, great! If not, also provide data. I cannot efficiently debug a formula without data. See directions below for providing data and expected results.

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

thanks so much that fixed the expression error but I'm still getting majority outpts as error, the odd Not Required appears.

The Today's Date column (which is a custom column) references to get the dates I have as - 

= DateTime.Date(DateTime.LocalNow()).

Would that be causing the error? Is that wrong?  Would I better adding the today's date colu,m in my excel rather than having it as a custom column?

Thanks so much

Again... provide data please. It is like asking a mechanic to fix your car over the phone based on the noises you are making. Thanks!

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

Top Solution Authors
Top Kudoed Authors