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.

     

AS_0001's avatar
AS_0001
Helper I
6 years ago

Thank you for your reply.

Even if I changed the query, I cannot get expected result. Returned figure is #Error.

I would like to calculate average per line based on column_1, column_2, column_3, column_4. I would like to exclude 0 for average calculation

 

IDcolumn_1column_2column_3column_4Average
AAA10001
BBB11111
CCC21101.33
DDD22111.5
  • AS_0001's avatar
    AS_0001
    Helper I
    6 years ago

    I just confirmed if I take following steps in Query and Data View, I got expected result. However it is not smart way and I need to create unnecessary columns. I hope someone provide the solution to resolve it. Thank you...

     

     

    1.<In Query> Convert 0 to Null in column1, column2, column3, column4

    2. <Data View>  Add new column "SUM_column1_4 = column1 + column2 + column3 + column4 

    3. <Data View> Add new column "count_column1" = IF (column1>0, 1)

    4. <Data View> Add new column "count_column2" = IF (column2>0, 1)

    5. <Data View> Add new column "count_column3" = IF (column3>0, 1)

    6. <Data View> Add new column "count_column4" = IF (column4>0, 1)

    7. <Data View> Add new column "counts_column1_4" = "count_column1" + "count_column2"+ "count_column3"+"count_column4"

    8. <Data View>Add new column "average of column1_4" = "SUM_column1_4"/ "counts_column1_4"

    • AS_0001's avatar
      AS_0001
      Helper I
      6 years ago

      ＠v-frfei-msft 

      Hello Community Support Team,

       

      I'd appreciate it if you could provide the solution.

      Thank you so much.

       

      Best regards,

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        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.