Forum Discussion
Helper ICreating a new column to count consecutive instances of another column
Hello
this could come from a different sort order after expanding.
here now a solution that should definitily work
let Source = #table ( {"Primary Key","Attendance Code","Attendance Date"}, { {"1","P","40544"}, {"1","P","40575"}, {"1","A","40603"}, {"1","P","40634"}, {"1","A","40664"}, {"1","A","40695"} } ), ToDate = Table.TransformColumns ( Source, { { "Attendance Date", each Date.From(Number.From(_)), type date } } ), CreateIndex = Table.Buffer(Table.AddIndexColumn ( ToDate, "FirstIndex", 1, 1 )), Group = Table.Group ( CreateIndex, {"Attendance Code"}, {{"AllRows", (tableint) => let AddIndex = Table.AddIndexColumn(tableint,"Index",1), AddColumn = Table.AddColumn(AddIndex,"New Column", each if _[Index]=Table.RowCount(AddIndex) then Table.RowCount(AddIndex) else null), DeleteIndex = Table.RemoveColumns(AddColumn,"Index") in DeleteIndex}}, GroupKind.Local, (group,current)=> if group[Attendance Code]=current[Attendance Code] then 0 else 1), DeleteOther = Table.SelectColumns(Group,{"AllRows"}), ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column","FirstIndex"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column","FirstIndex"}), ChangeType = Table.TransformColumnTypes(ExpandTable,{{"New Column", Int64.Type}, {"Attendance Date", type date}, {"Attendance Code", type text}, {"Primary Key", type text}, {"FirstIndex", Int64.Type}}), Sort = Table.Sort(ChangeType,{{"FirstIndex", Order.Ascending}}), DeleteFirstIndex = Table.RemoveColumns(Sort,{"FirstIndex"}) in DeleteFirstIndexCopy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Community ChampionHello davidgaribaldi
You have to use this syntax for the last step. Please check also if your first column is named like "Primary key" .. otherwise change that too. The error was caused by different column names, meaning, you have provided data example with the wrong column names.
ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column"})
Hope it helps
Jimmy
Helper I
Alright, I changed my columns headers to what I originally said they were and used your script but am still gettings the errors. There is something about the expanding that it just doesn't like. When I go to the data view it loses all of the primary keys for some reason.
- Jimmy801
Hello
this could come from a different sort order after expanding.
here now a solution that should definitily work
let Source = #table ( {"Primary Key","Attendance Code","Attendance Date"}, { {"1","P","40544"}, {"1","P","40575"}, {"1","A","40603"}, {"1","P","40634"}, {"1","A","40664"}, {"1","A","40695"} } ), ToDate = Table.TransformColumns ( Source, { { "Attendance Date", each Date.From(Number.From(_)), type date } } ), CreateIndex = Table.Buffer(Table.AddIndexColumn ( ToDate, "FirstIndex", 1, 1 )), Group = Table.Group ( CreateIndex, {"Attendance Code"}, {{"AllRows", (tableint) => let AddIndex = Table.AddIndexColumn(tableint,"Index",1), AddColumn = Table.AddColumn(AddIndex,"New Column", each if _[Index]=Table.RowCount(AddIndex) then Table.RowCount(AddIndex) else null), DeleteIndex = Table.RemoveColumns(AddColumn,"Index") in DeleteIndex}}, GroupKind.Local, (group,current)=> if group[Attendance Code]=current[Attendance Code] then 0 else 1), DeleteOther = Table.SelectColumns(Group,{"AllRows"}), ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column","FirstIndex"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column","FirstIndex"}), ChangeType = Table.TransformColumnTypes(ExpandTable,{{"New Column", Int64.Type}, {"Attendance Date", type date}, {"Attendance Code", type text}, {"Primary Key", type text}, {"FirstIndex", Int64.Type}}), Sort = Table.Sort(ChangeType,{{"FirstIndex", Order.Ascending}}), DeleteFirstIndex = Table.RemoveColumns(Sort,{"FirstIndex"}) in DeleteFirstIndexCopy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy - Jimmy801
Hello davidgaribaldi
are you sure "Primary Key" is exactly written like this in your database? Be aware that Power Query is case sensitive.
Jimmy
- davidgaribaldi
Yes, Primary Key is written exactly like that. My best guess is during the grouping process, the primary key is lost because it loses connection with the rest of the data. I'm not sure but as it I can't use this script which is a shame because it clearly works. I just wish Power BI gave actual error codes so you knew what was going on but if you aren't experiencing this when you Close and Apply, I'm at a loss.
- Jimmy801
Hello davidgaribaldi
have revised the code.
let Source = #table ( {"Primary Key","Attendance Code","Attendance Date"}, { {"1","P","40544"}, {"1","P","40575"}, {"1","A","40603"}, {"1","P","40634"}, {"1","A","40664"}, {"1","A","40695"} } ), ToDate = Table.TransformColumns ( Source, { { "Attendance Date", each Date.From(Number.From(_)), type date } } ), Group = Table.Group ( ToDate, {"Attendance Code"}, {{"AllRows", (tableint) => let AddIndex = Table.AddIndexColumn(tableint,"Index",1), AddColumn = Table.AddColumn(AddIndex,"New Column", each if _[Index]=Table.RowCount(AddIndex) then Table.RowCount(AddIndex) else null), DeleteIndex = Table.RemoveColumns(AddColumn,"Index") in DeleteIndex}}, GroupKind.Local, (group,current)=> if group[Attendance Code]=current[Attendance Code] then 0 else 1), DeleteOther = Table.SelectColumns(Group,{"AllRows"}), ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Attendance Code", "Attendance Date","New Column"}, {"Primary Key", "Attendance Code", "Attendance Date", "New Column"}), ChangeType = Table.TransformColumnTypes(ExpandTable,{{"New Column", Int64.Type}, {"Attendance Date", type date}, {"Attendance Code", type text}, {"Primary Key", type text}}) in ChangeTypeHope it works now
Jimmy
- davidgaribaldi
It does look like it's now loading without errors so that's good!
but now I can actually test the data and it looks like it's wrong. As you can see those 3 A's aren't being counted as a unit.
- davidgaribaldi
Jimmy801 Seriously thank you so much, this looks like it did the trick. Thank you again for taking all this time to work with me. It is very appreciated!
- Jimmy801