Power Query to exclude NULL value

[AS_0001]

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.

My idea has two steps in general

1st step is to change replaced value from 0 to null in query

2nd step is to use "List. average".

-----Excerpt from Advanced Editor in Query-----

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",0,null,Replacer.ReplaceValue,{"column_1"}),
   #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value",0,null,Replacer.ReplaceValue,{" column_2"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value",0,null,Replacer.ReplaceValue,{" column_3"}),
   #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value",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"

--------

When I tried to apply, I got a following error message. I'd appreciate it if PBI expert provide the support for my issues.

----Error message-

Expression.Error: 4 arguments were passed to function which expects between 1 and 2.
Details:
Pattern=
Arguments=List

-----

[v-frfei-msft]

Hi  @AS_0001 ,

[AS_0001]

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

ID	column_1	column_2	column_3	column_4	Average
AAA	1	0	0	0	1
BBB	1	1	1	1	1
CCC	2	1	1	0	1.33
DDD	2	2	1	1	1.5

[AS_0001]

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]

＠@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 · Accepted solution]

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]

Thank you for your support! I resolved the solution finally.

[v-frfei-msft]

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]

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.