How to calculate and insert number of Columns as a Column in a Table

[Glenn_2022]

Hello all,

Hope you are well.

Would kindly need some advice on the below:

Need to put in the Yellow and Green columns in a Matrix table in Power BI dashboaard.

Yellow: Count of Columns (Outlet in this case) that as values for the particular Row

Green: Average Sales per outlet for each Row (based on # of outlets).

Any help is very much appreciated. Thank you!

Glenn

[Kishore_Kadhir · Accepted solution]

Hi @Glenn_2022 

Please open your advanced editor from power query and paste the below M code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRMjQwQCFjdSDyyaUlCkZAMSOwDIRUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sales = _t, #"outlet1 " = _t, outlet2 = _t, outlet3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", type text}, {"outlet1 ", Int64.Type}, {"outlet2", Int64.Type}, {"outlet3", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.ColumnNames(#"Changed Type")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Columns", {"outlet1 ", "outlet2", "outlet3"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Only Selected Columns", {"Sales"}, {{"All", each _, type table [Sales=nullable text, Attribute=text, Value=number]}}),
#"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Average Value", each List.Average([All][Value])),
#"Added Custom1" = Table.AddColumn(#"Added Custom3", "Count", each Table.RowCount([All])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"All"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns1", {"Sales"}, #"Removed Columns", {"Sales"}, "Sales (2)", JoinKind.LeftOuter),
#"Expanded Sales (2)" = Table.ExpandTableColumn(#"Merged Queries", "Sales (2)", {"outlet1 ", "outlet2", "outlet3"}, {"outlet1 ", "outlet2", "outlet3"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Sales (2)",{"Sales", "outlet1 ", "outlet2", "outlet3", "Average Value", "Count"})
in
#"Reordered Columns"

Hope it helps.

Regards,

Kishore