Forum Discussion
Index column based on column value in grouped data
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.
Hi Anonymous,
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
5 Replies
=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})
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.Hi Anonymous,
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
