Accumulate Row count Power Query Editor + DAX

[micjensen]

Hi

I have a very simple data table with a unique perosn ID and a date stamp. What i want to do is to create a column that indicates the accumulated row count for each unique person id - i don't want to create a measure, but a column.

Below is my Data table and the other picture includes the column that i want to create. I would be very grateful if anybody would have the solution in DAX and in power query editor as well 🙂

Thx a lot in advance.

[BA_Pete · Accepted solution]

Hi @micjensen ,

In Power Query you can group the table with the All Rows operation then add an index to the nested tables:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjY6VYnWglJxxCRihCzpiqHHGowiJkjGm8MaZZWFSZIIRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PersonID = _t, Date = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"PersonID", type text}, {"Date", type date}}),
    sortRows = Table.Sort(chgTypes,{{"PersonID", Order.Ascending}, {"Date", Order.Ascending}}),
    groupPersonID = Table.Group(sortRows, {"PersonID"}, {{"data", each _, type table [PersonID=nullable text, Date=nullable date]}}),
    addNestedIndex = Table.TransformColumns(groupPersonID, {"data", each Table.AddIndexColumn(_, "Count", 1, 1)}),
    expandNestedData = Table.ExpandTableColumn(addNestedIndex, "data", {"Date", "Count"}, {"Date", "Count"})
in
    expandNestedData

For DAX, I would first sort your rows by [PersonID] and [Date] and add an index column IN POWER QUERY.

Then the following should work:

Count =
CALCULATE (
    COUNT(yourTable[PersonID]),
    ALLEXCEPT(yourTable, yourTable[PersonID]),
    yourTable[Index] <= EARLIER(yourTable[Index])
)

Pete

[micjensen]

Hi Pete

Thanks a lot for your explanatory reply. Much appreciated 🙂 !!