Index column based on column value in grouped data

[nicplancho]

Hi,

I'm trying to generate an index column in grouped data based on a text value.

Here is the raw data :

TeamProject categoryProject typeRoleMonthValue

SW Licensing	Roadmap	VS Installation & Onboarding	Lead	August 2022	0
SW Licensing	Roadmap	VS Installation & Onboarding	Lead	September 2022	0
SW Licensing	Roadmap	VS Installation & Onboarding	Lead	October 2022	0

What I currently have is this :

#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Source.Name", type text},{"Team", type text}, {"Project category", type text}, {"Project type", type text}, {"Role", type text}, {"Month", type text}, {"Value", type number}}),

#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}),

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "#FCST", 1, 1))

Here is a subtable :

What I would like is very similar, but I would like the index (#FCST here) on the subtable to be incremented only when the value in Source.Name changes.

In this case the first 3 rows should stay as 1 and the 4th row should be 2.

I have the intuition that it may be easy to do, but I don't have enough knowledge to properly solve the issue 😞

Would you be able to help me out on this topic please ?

Thanks a bunch,

Nicolas.

[wdx223_Daniel]

=Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},"",0},(x,y)=>{x{0}&y&[#"#FCST"=x{2}+Byte.From(y[#"Source.Name"]<>x{1})],y[#"Source.Name"],x{2}+Byte.From(y[#"Source.Name"]<>x{1})}){0})

[nicplancho]

Thanks for your answer Daniel !

#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Source.Name", type text}, {"Team", type text}, {"Project category", type text}, {"Project type", type text}, {"Role", type text}, {"Month", type text}, {"Value", type number}}),

#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}),

#"Added Custom"  = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "#FCST", 1, 1)),

#"Added Custom1" = Table.FromRecords(List.Accumulate(Table.ToRecords(#"Added Custom"),{{},"",0},(x,y)=>{x{0}&y&[#"#FCST"=x{2}+Byte.From(y[#"Source.Name"]<>x{1})],y[#"Source.Name"],x{2}+Byte.From(y[#"Source.Name"]<>x{1})}){0})

Output :

Expression.Error: Evaluation resulted in a stack overflow and cannot continue.

[jbwtp · Accepted solution]

Hi @nicplancho,

What about something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVHBCsIwDP2V0LMbs4r3wQ4KHmQ7zh26LY5B7UabgP69a2UnFUV2SHi8kPfySFmK/MxJIncZ5OgGtg3CSStjetNBBCl37AhkImV8064GsRKZIgWpUfpOfeM8o83FKg/2yrYYVp+iUPA4Dpb8bJYPG5NuJOUE1qGq1SJ3HOY7AnBkuSG26JkjqvbFWC5lrD8bv4+9iUP/7l/g**bleep**0f6c/b8fbEVVPQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source. Name " = _t, #"Team " = _t, #"Project category " = _t, #"Project type " = _t, #"Role " = _t, #"Month " = _t, #"Value " = _t, #"#FCST " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source. Name ", type text}, {"Team ", type text}, {"Project category ", type text}, {"Project type ", type text}, {"Role ", type text}, {"Month ", type date}, {"Value ", type number}, {"#FCST ", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Source. Name "}, {{"Count", each _, Value.Type(#"Changed Type")}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"Team ", "Project category ", "Project type ", "Role ", "Month ", "Value ", "#FCST "}, {"Team ", "Project category ", "Project type ", "Role ", "Month ", "Value ", "#FCST "})
in
    #"Expanded Count"

Kind regards,

John

[nicplancho]

Hi jbwtp,

Thanks for your proposal.

Input :

#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Source.Name", type text}, {"Team", type text}, {"Project category", type text}, {"Project type", type text}, {"Role", type text}, {"Month", type text}, {"Value", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "#FCST", 1, 1)),
#"Grouped Rows1" = Table.Group(#"Added Custom", {"Source. Name "}, {{"Count", each _, Value.Type(#"Added Custom")}})
in

#"Grouped Rows1"

Output :

Expression.Error: The column 'Source. Name ' of the table wasn't found.

[jbwtp]

Hi @nicplancho,

on the step #"Grouped Rows"  the code leave only those columns: "Team", "Project category", "Month" & "Count". Then another one is added - "Custom". There is no "Source.Name" column any more in the table when we come to the #"Grouped Rows1", hence the error. But from what I can see you do not need it. Most likely this is an artifact from my code sample, in this case it seems to be redundant in your version of the code. You should be able to remove it and do "Expand Columns" [on the Count] instead.

Cheers,

John