Calculate MIN Column to Exclude <blank>

[SofaQueen]

Hello,

Looking for some help with calculating my MIN column so when calculating a range, any zeros are included & <blank> values excluded.

I have a dataset of 1-8 points, each in a column different for each part number and a SUBGROUP colum to reflect how many of these datapoints are in each row, which varies.

Based on those 8 data columns, I have a column for MIN & one for MAX, then calculated MAX-MIN=RANGE

The issue with the MIN column is the <null/blank> value being translated from the data server comes through as a value  of -1.6E+98, so the correct value is only displayed when all 8 datasets are full. 

Thanks!

[SofaQueen]

Interesting that my post can have a solution accepted by someone else... and also my comment on that solution stated- though very appreciative of the assistance & idea, I need my <blanks> to still be blank & not a zero which would disrupt calculations.  Don't see that comment either & unable to respond in the accepted area, so that is why I'm posting this here.

I hope the effort put in for the marked solution will help someone else & I may look into using the info in other areas, however my issue is not resolved.  Thank you.

[v-kelly-msft · Accepted solution]

Hi @SofaQueen ,

First go to power query,replace all the -1.6E+98 to 0 using below M codes.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jXUMzNw1ba0UNJRMjVTitWJBlE6SOKxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATA3 = _t, DATA4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATA3", type number}, {"DATA4", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",-1.6E+98,0,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type"))
in
    #"Replaced Value"

And you will see:

Then you can get the correct value as you need.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @SofaQueen 

you need to use MINX and MAXX

like MINX(table[column],not(ISBLANK(table[column])))

and 

MAXX(table[column],not(ISBLANK(table[column])))