Relative Count If M Language WITHOUT Groupig

[lipesouza12]

Hello everybody,

I have a question that may have a simple answer but I don't see it.

I need an indexer that identifies how many times a given row repeats. Working exactly like rexcel's relative COUNTIF "=countifs($X1:X1;"01.1";...)"

The original table has single rows where I need to, variably, duplicate it N times. For that, I'm using Table.Repeat

To relate it to another table, I need a unique identifier that identifies the 1st time the row appears, the 2nd, 3rd and so on... I need to create a index by row or table, since the table is repeated N times

COND. 1	COND. 2	COND. 3	COUNT IF
A	T207	02.1	1
A	T207	02.2	1
B	T207	02.3	1
A	T207	02.1	2
A	T207	02.2	2
B	T207	02.2	3
A	101	02.1	1

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoxMjAHUgZGeoZKsTroYkZgMScUMWMs6ojVawRXZ2hgCNcaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COND. 1" = _t, #"COND. 2" = _t, #"COND. 3" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"COND. 2", "COND. 3"}, {{"Temp", each _, type table [COND. 1=nullable text, COND. 2=nullable text, COND. 3=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Temp], "COUNT IF", 1, 1, Int64.Type)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"COND. 1", "COUNT IF", "Index"}, {"COND. 1", "COUNT IF", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"COND. 1", "COND. 2", "COND. 3", "COUNT IF"})
in
    #"Reordered Columns"

[lipesouza12]

@Vijay_A_Verma 

I've tried both methods and your method's performance is much more efficient. Using a buffered table works fine with small bases, but using it for the base I intended performance drops taking hours of processing.

Thanks again, it's saved me tons of hours of work!

[lipesouza12]

Thanks a lot Vijay! Its works perfectly! ‌‌

However, as soon as I posted the question, I found another solution, but it's takes 1 minute to index 1.000 rows. I will test both methods for performance checking, since the table has 200 to 300 thousand rows.

let
    Repetition = Excel.CurrentWorkbook(){[Name="YEARS"]}[Content][Column1]{0},
    #"ℹ - Source" = Table.Repeat(Excel.CurrentWorkbook(){[Name="TableTest"]}[Content], Repetition ),
    #"⯐ - Temp Index" = Table.AddIndexColumn(#"ℹ - Source", "Index", 1, 1, Int64.Type),
    BufferedTable = Table.Buffer(#"⯐ - Temp Index"),
    #"∳ - Single row count" = Table.AddColumn(BufferedTable, "Count",

             (OT) => Table.RowCount(Table.SelectRows(BufferedTable,

             (IT) => IT[Index] <= OT[Index] and

                 IT[COND. 1] = OT[COND. 1] and

                 IT[COND. 2] = OT[COND. 2]and

                 IT[COND. 3] = OT[COND. 3])

             )

),
    #"⯐ - Indexer" = Table.AddColumn(#"∳ - Single row count", "INDEXER", each Text.From([Count]) & "-" & [COND. 3]),
    #"Remove Columns" = Table.RemoveColumns(#"⯐ - Indexer",{"Index", "Count"})
in
#"Remove Columns"

Follow link: https://www.youtube.com/watch?v=kjOmNPoVDYs