Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

Power Query | Countif equivalent Fixed Reference

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.