Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 e...
Need Help
Show and Tell
  • HotChilli's avatar
    HotChilli
    4 years ago

    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's avatar
HotChilli
Community Champion
4 years ago

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