Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am working on setting up a report for someone that needs to show the number of days between submission and completion of a request form. The issue that i am running into is that there are 2 completion date columns, one for completion of the overall form and the other for completion of the specific item I need to report on. All cells in the overall form column are filled in, but only some of the cells for the specific item column are filled in. I need to overwrite the null values in the second column with the values in the first column.
This is a sample of the data:
I have been researching to try to get this working and come up with the below coding in order to make this happen:
= Table.ReplaceValue(#"Changed Type", each [CACS 9 Completed], each if [CACS 9 Completed] = "null" then each [Date Completed] else [CACS 9 Completed], Replacer.ReplaceValue,{CACS 9 Completed})
This returns an error:
Expression.SyntaxError: Token Comma expected.
If I adjust the coding to add in a comma:
= Table.ReplaceValue(#"Changed Type", each [CACS 9 Completed], each if [CACS 9 Completed] = "null", then each [Date Completed] else [CACS 9 Completed], Replacer.ReplaceValue,{CACS 9 Completed})
I get:
Expression.SyntaxError: Token Then expected.
Can anyone help with any insight into where I'm going wrong with either coding items to achieve what I need?
= Table.ReplaceValue(#"Changed Type", each [CACS 9 Completed], each if [CACS 9 Completed] = null then [Date Completed] else [CACS 9 Completed], Replacer.ReplaceValue,{CACS 9 Completed})
lose the double quotes around null, and the second each. Or use the COALESCE equivalent in Power Query
= Table.ReplaceValue(#"Changed Type", each [CACS 9 Completed], each [CACS 9 Completed] ?? [Date Completed], Replacer.ReplaceValue,{CACS 9 Completed})
I've tried both of your suggestions, and it is still returning token comma error
please post the sample data in usable form.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |