Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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"
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"
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.