cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
opticshrew Member
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:

Table1.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
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

1 REPLY 1
v-huizhn-msft Super Contributor
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