Split column into different columns by different elements

[Syndicate_Admin]

Good morning

I have a table with the following format:

ID

NUMBER

TYPE ACTIVITY

One

Feb

Mar

Apr

May

Jun

Jul

Ago

Sep

Oct

Nov

Dec

The table has the hours of each month in each of the activities, each id has hours in the different activities and the accumulated hours that go.

In type of activity there are 4 different types with completely different names (they have spaces between the words, I can not divide by delimiters), I would like to obtain a table with a column for each type of activity and as data the sum of the hours of the last three months.

ID

NUMBER

Activity 1

Activity 2

Activity 3

Activity 4

I guess it's possible to do it, someone to guide me with the solution?

Thanks a lot

[Syndicate_Admin]

Original data table:

ID	NUMBER	TYPE ACTIVITY	Jul	Ago	Sep	Oct	Nov	Dec	One	Feb	Mar
100	F	A	1		2		1		1
101	M	A	1			1		1		2	2
102	G	B		1		30	8	14	30	8	14
102	G	A			2	42	41	57	42	41	57
102	G	C	1			4	29	28	4	29	28
102	G	D		2	3		2	1		2
102	G	C	24	51	35	24	51	35	1		4
103	N	B							2	18	36
103	N	A	4	0	12	4	0	12	13	28	33
103	N	C							24	51	35
103	N	D	7			7
103	N	C							4	0	12
104	R	B	44	20	63	44	20	63
104	R	A				7			7
104	R	C	28	26	31	28	26	31
105	J	B	36	29	27	36	29	27	44	20	63
105	J	A	5	5	6	5	5	6	36	31	34
105	J	C	2	1		2	1		15	35	14
105	J	D
106	K	B	8	14		28	26	31	28	26	31
106	K	A	41	57		36	29	27	36	29	27
106	K	C	29	28		5	5	6	5	5	6
106	K	D	2	1		2	1		2	1

Expected table (sum of the hours of the last three months of each id and activity):

ID	NUMBER	A	B	C	D
100	F	1		2
101	M	4
102	G		50,9
102	G	139,9		2
102	G	1		61,19
102	G		2	3
102	G	23,8	51,3	4,6
103	N		55
103	N	73
103	N			109
103	N
103	N			16,351
104	R
104	R	6,952
104	R
105	J		127
105	J	101
105	J			62,244
105	J
106	K		85
106	K	94
106	K			16,552
106	K	3,1117

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

I suggest you to try code as below in advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVNBbsMgEPwLZx8Ca3B6dLFSqVV76DXy/79RBgNmlkSp5LVZe2Zndwz3u7GXi5nMLcWawqZIlzselh5mnwBH9s3wAepyHHisP1K8E0KgekU+q4xZa1PAmznfUMIvOmVepO6g4d5wu6qMWdspJeeyN2VUcSjngRE/prY2cBBR9ec0Q11ZDB1KUPi1tA2frNOJlTqaiCLGx0J9k0yABUsDLpr7n+pdbwccL37L1HN2H5+DjCmpVNraFx97Y3CsVriA8eyYEg3/6LO0JqFtjGVMqVMmo0FfIqi1NF2ZFS2O+6seI9/2jyZtDy2nmaD4VWZqR+yFLczM260dsxfWMDWaqTtqT41h0vbUibbc9z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, NUMBER = _t, #"TYPE ACTIVITY" = _t, Jul = _t, Ago = _t, Sep = _t, Oct = _t, Nov = _t, Dec = _t, One = _t, Feb = _t, Mar = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"NUMBER", type text}, {"TYPE ACTIVITY", type text}, {"Jul", Int64.Type}, {"Ago", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}, {"One", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Jul", "Ago", "Sep", "Oct", "Nov", "Dec", "One", "Feb", "Mar"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"ID", "NUMBER", "TYPE ACTIVITY"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "YearMonth", each if [Attribute] = "Jul" then 202207
else if [Attribute] = "Ago" then 202208
else if [Attribute] = "Sep" then 202209
else if [Attribute] = "Oct" then 202210
else if [Attribute] = "Nov" then 202211
else if [Attribute] = "Dec" then 202212
else if [Attribute] = "One" then 202301
else if [Attribute] = "Feb" then 202202
else if [Attribute] = "Mar" then 202203
else null),
    #"Grouped Rows1" = Table.Group(#"Added Custom", {"ID", "NUMBER", "TYPE ACTIVITY", "Attribute", "YearMonth"}, {{"Sum1", each List.Sum([Value]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"NUMBER", Order.Ascending}, {"TYPE ACTIVITY", Order.Ascending}, {"ID", Order.Ascending}, {"YearMonth", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID", "NUMBER", "TYPE ACTIVITY"}, {{"Count", each _, type table [ID=nullable number, NUMBER=nullable text, TYPE ACTIVITY=nullable text, Attribute=text, Value=number, YearMonth=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Attribute", "YearMonth", "Sum1", "Index"}, {"Custom.Attribute", "Custom.YearMonth", "Custom.Sum1", "Custom.Index"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Sum2", each let 
_LastMonth = List.Max(Table.SelectRows(#"Expanded Custom",
each [ID] = _ID and[NUMBER] = _NUMBER and [TYPE ACTIVITY] = _TYPE
)[Custom.Index]),
_ID =[ID],
_NUMBER = [NUMBER],
_TYPE = [TYPE ACTIVITY],
_Index = [Custom.Index]
in
if [Custom.Index] = _LastMonth
then
List.Sum(Table.SelectRows(#"Expanded Custom",
each [ID] = _ID and[NUMBER] = _NUMBER and [TYPE ACTIVITY] = _TYPE and [Custom.Index] >=_Index -3)[Custom.Sum1])
else
null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Sum2] <> null and [Sum2] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Custom.Attribute", "Custom.YearMonth", "Custom.Sum1", "Custom.Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"ID", "NUMBER", "Index", "TYPE ACTIVITY", "Sum2"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[#"TYPE ACTIVITY"]), "TYPE ACTIVITY", "Sum2", List.Sum)
in
    #"Pivoted Column"

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mahesh0016]

@Syndicate_Admin Please Share Your Dummy Data for better understanding.THANK YOU!!