Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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")
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthanks 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting