Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
What is the correct syntax or a similar method to achieve a countif with a fixed reference at the first row
=COUNTIF(M$1:$M3,M3)
As you can see this formula expands as the row progresses... but how do I do it in M?
Solved! Go to Solution.
First add an index column. Next add a column that pulls the M column from the #"Added Index" step and use List.Select to pull all values matching M (of the current step). Use List.Count to count the number of rows. Now you can remove the INdex column. Query is below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyEYmEi8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [M = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
AddCountIf = Table.AddColumn(#"Added Index", "CountIf", each List.Count(List.Select(List.FirstN(#"Added Index"[M],[Index]), (el) => el = [M]))),
#"Removed Columns" = Table.RemoveColumns(AddCountIf,{"Index"})
in
#"Removed Columns"
First add an index column. Next add a column that pulls the M column from the #"Added Index" step and use List.Select to pull all values matching M (of the current step). Use List.Count to count the number of rows. Now you can remove the INdex column. Query is below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSgKTyWAyEYmEi8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [M = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
AddCountIf = Table.AddColumn(#"Added Index", "CountIf", each List.Count(List.Select(List.FirstN(#"Added Index"[M],[Index]), (el) => el = [M]))),
#"Removed Columns" = Table.RemoveColumns(AddCountIf,{"Index"})
in
#"Removed Columns"
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |