Creating a new column to count consecutive instances of another column

[davidgaribaldi]

Alright, I feel stupid for multiple reasons but foremost I can't find how to upload my data so if you have some help on that I'd be happy to upload it.

I have a python script that does what I want but I'm trying to move everything into Power BI for ease of use.

I have three columns: Primary Key, Dates, and a code.

I want to count consecutive instances of each code so if it appears 3 times in a row (in 3 consecutive rows) I want the last instance of it to say 3 in a new 4th column.

Primary Key	Code	Attendance Date	New Column
1	P	1/1/11
1	P	1/2/11	2
1	A	1/3/11	1
1	P	1/4/11	1
1	A	1/5/11
1	A	1/6/11	2

[Jimmy801]

Hello @davidgaribaldi 

this solution requires some customized m-code as it would not be possible to do this with the UI. Involves some Table.Group with GroupKind.Local and a special handling of the table. Here the complete solution

let
	Source = #table
	(
		{"Primary Key","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, 
        {"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, type table [Primary Key=text, Code=text, Attendance Date=date]}},
        GroupKind.Local,
        (group,current)=> if group[Code]=current[Code] then 0 else 1),
    DeleteOther = Table.SelectColumns(Group,{"AllRows"}),
    ExpandTable = Table.ExpandTableColumn(DeleteOther, "AllRows", {"Primary Key", "Code", "Attendance Date","New Column"}, {"Primary Key", "Code", "Attendance Date", "New Column"})
in
	ExpandTable

it all happens in the table.group function. Check it out here

Copy 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

[davidgaribaldi]

Sorry but when I close and apply it comes up with errors for each row. I figured out it doesn't like the ExpandColumns step. Do you have any ideas why that might be? When I go to look at the errors it says there are none.

[Jimmy801]

Hello @davidgaribaldi 

whitch error? Could it be that you have different column names?

Jimmy

[davidgaribaldi]

0 errors when I actually go and look at the errors.A lot of errors when I close and applyIt says I have 42,367 errors but then when I go to see the errors it can't find any. I removed the Expand Columns step and the errors went away so it must be something in that step. I think it might be tacking the New Column onto the table even though it wasn't in the group step that's causing the error.

[Jimmy801]

Hello @davidgaribaldi 

could you please go to the DeleteOther-step and click on one table in the "AllRows"-column with a few lines to goto this table. Check out if the there are some errors and please make a screenshot of it.

Jimmy

[davidgaribaldi]

I clicked on a couple and there doesn't appear to be any errors. All the data is correctly calculating.

*EDIT: Okay I just expanded the columns without adding the New Column and it still creates those errors so just the act of expanding is creating errors somewhere.

[Jimmy801]

Hello @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

[davidgaribaldi]

@Jimmy801 

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 @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
	ChangeType

Hope 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.

[Jimmy801 · Accepted solution]

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
	DeleteFirstIndex

Copy 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

[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]

Hello @davidgaribaldi 

your feedback is very appreciated

Glad it did work out

Jimmy

[davidgaribaldi]

Well done! Yes, I entered my source as my table and it worked perfectly. Thanks a lot!

[Jimmy801]

Hello @davidgaribaldi 

your feedback is very appreciated

All the best

Jimmy