Forum Discussion
Write value on every 5 rows
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?
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
7 Replies
Anonymous providing sample data and desired output would be a good start
Yes, with sort function, custom conditional index column and math.
Please provide some data.
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
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
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
