Forum Discussion
Write value on every 5 rows
Yes, it's achievable. Sort the data on 2 columns (text, date).
Then do a 'Group By' on Text, using the All Rows aggregation.
Then add an index within each group with code similar to this:
Add a custom column
Table.AddIndexColumn([all], "sub", 1, 1, Int64.Type)[all] is the name of the All Rows column from the previous step.
Remove the columns that you don't want and expand the column headers to return the required data.
Good luck
Make 2 copies of the table (using Duplicate from the interface)
1st table : Filter out rows with text in Column2 (from the column header).
2nd table : Filter out rows with no text in Column2. Sort the Date column ascending. Add an Index (from Add Column menu).
Add a column (with your column name inserted) :
if Number.Mod([IndexCol], 5) = 0 then "SKIP LOT" else ""
---
Append the 2 tables (from the interface)
--
That should do it. Let me know how it goes
Hello, Thanks a lot it worked. Do you know if there is this complication: we have different texts, and I want to index based on the value "texts".
For the first Step I will sort the rows based on ascending date. But how can I index based on the text value? Example is below
For instance:
Text Value Date Index Text A 02/12/2021 2 Text A 01/12/2021 1 Text B 01/12/2021 1 Text C 01/12/2021 1 Text B 02/12/2021 2 Text C 02/12/2021 2 Text A 03/12/2021 3 Text A 04/12/2021 4