cancel
Showing results for
Did you mean:
Highlighted
Member

## Showing blank values as zero when the data doesn't exist

Hi all,

I have a table that contains a list of items with there associated values:

The issue I am having is that because the values for Sol and Oth are blank they are not being sorted by the column I have specified. ( I have a custom column that labels each item as 1 -> 5 )

The values should be ordered as:

Con, Man, Sol, Pro, Oth

Usually I would have something in place that would set all blank() to 0 however because the data is coming from two seperate tables it's a little trickier.

The Line column is in a seperate table to the Order column.

J

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Showing blank values as zero when the data doesn't exist

Hi @opticshrew,

Based on my understanding, althrough the Line and Order columns come from different tables. There must have a relationship between them, right? Otherwise, we are unable to create a table like your given.

You can create a calculated column in the table where the "Line" field locates using the formula.

`New Order=IF(ISBLANK(RELATED(Table2[Order])),0,RELATED(Table2[Order]))`

Then you can create create your table based on the new calculated column.

If this is what you want, please share more details for further analysis.

Best Regards,
Angelia

Super Contributor

## Re: Showing blank values as zero when the data doesn't exist

Hi @opticshrew,

Based on my understanding, althrough the Line and Order columns come from different tables. There must have a relationship between them, right? Otherwise, we are unable to create a table like your given.

You can create a calculated column in the table where the "Line" field locates using the formula.

`New Order=IF(ISBLANK(RELATED(Table2[Order])),0,RELATED(Table2[Order]))`

Then you can create create your table based on the new calculated column.

If this is what you want, please share more details for further analysis.

Best Regards,
Angelia