Forum Discussion
count if
Hi, I would like to get the effect as below, the value is calculated based on the two id's from the first two columns.
| id | second_id | count |
| a | 1 | 1 |
| a | 1 | 2 |
| a | 1 | 3 |
| a | 2 | 1 |
| b | 1 | 1 |
| b | 2 | 1 |
| b | 2 | 2 |
| b | 2 | 3 |
| c | 1 | 1 |
add an index column in power query https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-biThen try a new column in dax
count = countx(filter(Table, [id] =earlier([id]) && [second_id] =earlier([second_id]) && [index] <= earlier([index]) ),[index])
2 Replies
add an index column in power query https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-biThen try a new column in dax
count = countx(filter(Table, [id] =earlier([id]) && [second_id] =earlier([second_id]) && [index] <= earlier([index]) ),[index])Hi wiktorius1984 ,
you can do it in Power Query like this:
// Table (2) let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitXBzTICs5LgYkkoYuisZIi6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, second_id = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, Table, {"Index.1"}, "Table", JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Index"}, {"Index.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Index"}) in #"Removed Columns" // Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitXBzTICs5LgYkkoYuisZIi6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, second_id = _t]), #"Inserted Merged Column" = Table.AddColumn(Source, "Merged", each Text.Combine({[id], Text.From([second_id], "de-DE")}, ""), type text), #"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"Merged"}, {{"Count", each _, type table [id=nullable text, second_id=nullable number, count=nullable number, Merged=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1, 1, Int64.Type)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Index", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index.1", 0, 1, Int64.Type) in #"Added Index"With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
