Forum Discussion
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.
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
-----
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.
8 Replies
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.
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.
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 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"
