Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello please help me.
I want caverage like this. i dont want considered number 0.
thanks
Solved! Go to Solution.
Try this @Daniff
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYlOlWJ1oJSMgyxKFZwDnmYBZMJ4xkGUG5xlAVRqAeYZQnoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, #"Another column" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"Another column", Int64.Type}}),
#"Added Average" =
Table.AddColumn(
#"Changed Type",
"Average",
each
let
varList = List.Select(Record.ToList(_), each _ > 0),
varAverage = List.Sum(varList) / List.Count(varList)
in
if varAverage = null then 0 else varAverage
)
in
#"Added Average"
It returns this:
Here is what it does:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello thanks for your reply. i want average only 2 selecion columns. i dont want average all columns. please help me.
@Daniff you need to supply sample data. You only gave two columns so the solutions provided average those columns. If you have 30 columns, you have to tell us how you want to identify the two columns. An embedded Record.SelectFields() will work, so you can add that yourself, or you an provide some good sample data per the links below and tell me which columns you want averaged.
You can try this, but if this doesn't specifically answer the question... then sample data. This only averages V1 and V2.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYlOlWJ1oJSMgyxKFZwDnmYBZMJ4xkGUG5xlAVRqAeYZQnoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, #"Another column" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"Another column", Int64.Type}}),
#"Added Average" =
Table.AddColumn(
#"Changed Type",
"Average",
each
let
varAverage =
List.Average(
List.Select(
Record.ToList(
Record.SelectFields(_, {"V1", "V2"})
),
each _ > 0)
)
in
varAverage ?? 0
)
in
#"Added Average"
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUse below formula in a custom column to calculate average without 0
= List.Average(List.RemoveItems(Record.ToList(_),{0}))
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHSUTIAYjOlWJ1oMAuCYTxjOM8UyDIECRkpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, V3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"V3", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Average(List.RemoveItems(Record.ToList(_),{0})), type number)
in
#"Added Custom"
Hello, many thanks, dont have any solution more easy? Thanks
Not that I can think of. This is just a relatively simple but compound formula in the Custom Formula box
I could do it without variables, but then I am repeading stuff many times and it start to look like a hideous Excel formula. This is the same thing, but impossible to read and uneditable.
if List.Sum(List.Select(Record.ToList(_), each _ > 0)) / List.Count(List.Select(Record.ToList(_), each _ > 0)) = null then 0 else List.Sum(List.Select(Record.ToList(_), each _ > 0)) / List.Count(List.Select(Record.ToList(_), each _ > 0))
There is no List.Average(math here, IgnoreZeros) function though.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Daniff - @Vijay_A_Verma reminded me about List.Average - I have never used it in practice, but it does make it simpler.
But the above will return null if all are 0. That may be desirable. If not, then you still want the if/then/else construct, so it only slightly simplifies the formula.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYlOlWJ1oJSMgyxKFZwDnmYBZMJ4xkGUG5xlAVRqAeYZQnoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, #"Another column" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"Another column", Int64.Type}}),
#"Added Average" =
Table.AddColumn(
#"Changed Type",
"Average",
each
let
varAverage = List.Average(List.Select(Record.ToList(_), each _ > 0))
in
if varAverage = null then 0 else varAverage
)
in
#"Added Average"
If null is ok when all are zero, the either @Vijay_A_Verma 's function or just List.Average(List.Select(Record.ToList(_), each _ > 0)) will work.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAnd... actually I remembered that Power Query has a coalesce function, so
List.Average(List.Select(Record.ToList(_), each _ > 0)) ?? 0
So if the average is null, that formula will report 0.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingTry this @Daniff
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYlOlWJ1oJSMgyxKFZwDnmYBZMJ4xkGUG5xlAVRqAeYZQnoVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [V1 = _t, V2 = _t, #"Another column" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"V1", Int64.Type}, {"V2", Int64.Type}, {"Another column", Int64.Type}}),
#"Added Average" =
Table.AddColumn(
#"Changed Type",
"Average",
each
let
varList = List.Select(Record.ToList(_), each _ > 0),
varAverage = List.Sum(varList) / List.Count(varList)
in
if varAverage = null then 0 else varAverage
)
in
#"Added Average"
It returns this:
Here is what it does:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting