Forum Discussion
Relative Count If M Language WITHOUT Groupig
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"
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"
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