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

[opticshrew]

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.

Thanks in advance,

J

[v-huizhn-msft · Accepted solution]

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