Index Countif

[MStark]

How can I create the LIne_NO in Power Query? Its counting what number time these 2 columns came up so far with the same data. Notice the $D$1:D2 and $C$1:C2 which causes it to start counting from the top but to only count up until the current line

Any assistance would be appreciated. Thanks!!

[dufoq3 · Accepted solution]

Hi @MStark,

you can use Group By with Add Index

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3tNQ3MjAyUdJRMlSK1YlWMtI3skAVQVZjRK5ILAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, LOCATION_ID = _t]),
    GroupedRows = Table.Group(Source, {"Date", "LOCATION_ID"}, {{"All", each Table.AddIndexColumn(_, "LINE_NO",1,1,Int64.Type), type table [Date=nullable text, LOCATION_ID=nullable text]}}),
    All = Table.Combine(GroupedRows[All])
in
    All

[MStark]

@dufoq3 This works!! Thank you so much!

Can you explain what what the query is doing so I understand how its working?

[dufoq3]

During grouping it adds index column called LINE NO into inner table.

[MStark]

Very Cool! Thank you so much @DU !!!

[dufoq3]

Enjoy 😉