Forum Discussion
Solved
Index Countif
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!!
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
5 Replies
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
