Trying to convert null values in an numeric column in an Excel spreadsheet in order to perform calculations on that column. Currently unable to find a work around
@cameronhobbs: I was working on some DataFlows today and saw your comment here so I took it as a challenge. The ideal would have been to use one input dataset, send it through a value mapper action that searches for null values in a column and replaces with 0, and then output one dataset. I tried to configure that, but you can't search for null values in a Value Mapper action (at least not on a column that is a whole number type - the Value Mapper step outputs this error if you try: Unexpected conversion error while converting value [string String] to type Integer.).
So.. here's a screenshot of what I did as a workaround that got it to work:
Step 1: Filter your column's null values into a separate branch using a Filter Row action.
Step 2: Add an Add Constants action to the null branch that adds a new column with whatever name (Zero Value is what I used) with a value set to 0.
Step 3: Add a Select Columns action to the null branch and select the columns you need except the column that holds the null values (the column you set the filter on in Step 1).
Step 4: Left join your filtered branch with your input dataset on your unique key.
Step 5: Combine the original column on which you set the filter in Step 1 with the constant column you added to the null rows in Step 2 usins a Combine Columns action.
Here's a link to the input dataset I used (Google sheet): http://bit.ly/null-dataflow-helper
Here's a screenshot of my input and output datasets:
Let me know what you think! I'm happy to post follow up screenshots of any of my action configurations if that would help.
@mdelorey thanks for the input! I appreciate you taking the time on this, its definitely a viable soution.
@cameronhobbs Glad I was able to help! Like I said, feel free to reach out to me if I can get you any other info. I'm anxious to hear if it works for you.
Great solution. well done
@mdelorey - I know it's been a couple years since this solution was provided but wondering if Domo alllows you to filter rows/set constants/combine columns for multiple instances in a single dataset?
We have 12-15 columns that have null values that we'd like to replace the w/0's; I'm trying to repeat step 5 (add a new column combine tile) for each new column after step 4 (left joining the dataset but it's not letting me connect the tiles.
@mdelorey - had to play with it a bit more to find out WHERE you can add them, but yes, it does allow for multiple instances of joining columns to replace null values. Thanks for the post, this has proven to be very helpful!
Sorry my reply won't have a graphic. Fastest way to do it for multiple rows is to use "set column type" to convert all numeric columns to text, use the "replace text" to convert nulls to zeros, & then "set column type" again to convert back into numeric types. 3 steps no matter how many columns you're replacing values for.
Thanks @JSensei, appreciate the followup on this! Have a great day...
Thanks @mdelorey and @JSensei for your responses. I've used @JSensei 's method a few times now. Would there be better performance using one method or the other for achieving the desired results?