Write value on every 5 rows

[Anonymous]

Dear Power Bi community,

Do you know how I can write in PowerQuery:

To index the rows based on their date ( earliest to latest) and to index them only if they have a special text value ( for example "text")

After the indexing is done, to write in another new column the value "text2" every 5 rows?

[HotChilli · Accepted solution]

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

[HotChilli]

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

[Anonymous]

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

[KNP]

Yes, with sort function, custom conditional index column and math.

Please provide some data. 

[Anonymous]

So everything is in the same table.  I want the results to look like this.

The index based on the column 2 if I have "text" and in a chronological order by date. And every 5 indexes, to write in the new Column : Skip Lot

Column 1	Column 2	Date	Index	new Colum
a
b	text	05/12/2021	2
c	text	06/12/2021	3
d
e	text	07.12/2021	4
f
g	text	08/12/2021	5	SKIP LOT
h	text	01/12/2021	1

[smpa01]

@Anonymous  providing sample data and desired output would be a good start

[Anonymous]