Forum Discussion
Filling blank rows
Hello PBi Community,
I've run into some issues trying to clean up my data. A sample table of my data is listed here
This is a sample of my data table
| Site Name | Date | Status | Meets Criteria |
| Nashville | 11/10/2020 | Verified | Y |
| N. Dallas | 09/22/2020 | N | |
| Danville | 2/5/2020 | N | |
| Clinch | 2/22/2020 | ||
| Danville | 6/6/2020 | Verified CVS Pharm | Y |
| Danville | 5/5/2020 | Verified | |
| Southern TN | 6/8/2020 | FAXED | N |
I need a few things done/cleared up on this table.
1) If the "Meets Criteria" column has an "N" or a "__" that means the order was not sent electronically. I need all these rows that are completely blank to have an "N" in the Meets Criteria column.
2) There are some rows where the order was "Verified" (in the STATUS column) but it doesn't have a "Y" in the Meets Criteria column. A lot of these rows have different words after "Verified" in the "Status" column. I also need all these rows to have a "Y" in the Meets Criteria column.
Would it be easier for me to create a new column to clean up this mess?
Would love any suggestions on this issue.
Thanks a bunch
MR
Anonymous , I agree with the suggestion of a new column by parry2k.
A New column like
new Meets Criteria =
Switch(True(),
[Meets Criteria] in {"N","-"} , "N",
search("Verified",[Status],,0) >0, "Y",
"N"
)
3 Replies
- amitchandak
Super User
Anonymous , I agree with the suggestion of a new column by parry2k.
A New column like
new Meets Criteria =
Switch(True(),
[Meets Criteria] in {"N","-"} , "N",
search("Verified",[Status],,0) >0, "Y",
"N"
) - parry2k
Anonymous I will 100% create a new column in PQ and put the logic there. Add a custom column and check all the conditions and add the value.
parry2k could you possibly walk me through how to I would do that?
