Forum Discussion

AS_0001's avatar
AS_0001
Helper I
6 years ago
Solved

Power Query to exclude NULL value

I'm really new for power BI Query so let me ask the question. I'd like to calculate average with ignoring "0" . But it is difficult to exclude it via DAX. Therefore I tried to challenge in query.  ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi AS_0001 ,

     

    Sorry for my late respond. Please check the following steps as below.

    1. Unpivot the table as below. (Select the ID column and unpivot Other columns).

     

    M code for your reference.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VNJRMgRiAziO1YlWcnJygoojMEjc2dkZyDZCEoeod3FxgYoj5GJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, column_1 = _t, column_2 = _t, column_3 = _t, column_4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"column_1", Int64.Type}, {"column_2", Int64.Type}, {"column_3", Int64.Type}, {"column_4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

     

    2. Create a measure as below and add it a matrix visual.

    Measure 2 = 
IF (
    ISINSCOPE ( 'Table (2)'[Attribute] ),
    SUM ( 'Table (2)'[Value] ),
    CALCULATE (
        AVERAGE ( 'Table (2)'[Value] ),
        FILTER ( 'Table (2)', 'Table (2)'[Value] <> 0 )
    )
)

     

     

    For more details, please check the pbix as attached.

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi AS_0001 ,

 

Please update your code as below to have a check

 

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",0,null,Replacer.ReplaceValue,{"column_1"}),
   #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",0,null,Replacer.ReplaceValue,{" column_2"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",0,null,Replacer.ReplaceValue,{" column_3"}),
   #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3",0,null,Replacer.ReplaceValue,{" column_4"}),
   #"Added Custom" = Table.AddColumn(#"Replaced Value4", "average value", each List.Average( [column_1],[column_ 2],[column_3],[column_4])),
   #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"average price per color", type number}}),

in   #"Changed Type"

 

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

AS_0001's avatar
AS_0001
Helper I
6 years ago

Thank you for your message.

Unfortunately, I cannot find difference between your suggested query and my query.

I would appreciate it if you could provide the difference from mine.

 

According to error message, my assumption is we may not be allowed to use 4 colums for average calculation.