CODEWITHSUNDEEP
excelpowerbipowerquery
sebastian wei
sebastian wei

Reputation: 81

How can I expand tables with row count in Power Query?

What I have

My original data looks like this:

Object      Feature1     Feature2
Obj1        1.2          2.8
Obj2        1.6          2.2
Obj3        1.6          2.2
Obj4        1.2          2.8
Obj5        1.2          2.8
...

What I want

I want to sort them and give each object a new name. Objects with the same features will be represented with the same number (1,2...). Different objects in each group will be identified with A,B,C... Like this:

Object      Feature1     Feature2     Name
Obj1        1.2          2.8          1A
Obj4        1.2          2.8          1B
Obj5        1.2          2.8          1C
Obj2        1.6          2.2          2A
Obj3        1.6          2.2          2B
...

What I have tried

So far I merged all features into a new column (merged). Then group the data by the column "merged". The group number can be add with an index column.

Merged     Tables     Index
1.22.8     Table      1
1.62.2     Table      2
...

After I expand the tables, they are sorted in the order I want with group names.

Object      Feature1     Feature2     Name
Obj1        1.2          2.8          1
Obj4        1.2          2.8          1
Obj5        1.2          2.8          1
Obj2        1.6          2.2          2
Obj3        1.6          2.2          2
...

But how can I add the letters A, B, C within each group?

Upvotes: 1

Views: 300

Answers (1)

horseyride
horseyride

Reputation: 21318

Try this in powerquery

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows1" = Table.Group(Source, {"Feature1", "Feature2"}, {{"data", each _, type table }}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows1", "Index", 1, 1, Int64.Type),
#"Expanded data1" = Table.ExpandTableColumn(#"Added Index", "data", {"Object"}, {"Object"}),
#"Grouped Rows" = Table.Group(#"Expanded data1", {"Feature1", "Feature2","Index"}, {{"data", each 
     let a= Table.AddIndexColumn(_, "Letter", 1, 1, Int64.Type),
     b=  Table.TransformColumns(a,{{"Letter", each  // convert # to letter
         (if _ <703 then "" else Character.FromNumber(64+Number.IntegerDivide(_-27,26*26)))
         &( if _ >26 then Replacer.ReplaceText(Character.FromNumber(64+ Number.Mod( Number.IntegerDivide(_-1,26) ,26) ), "@", "Z") else "" )
         &(Character.FromNumber(65+Number.Mod(_-1,26)))
}}) in b, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Object", "Letter"}, {"Object", "Letter"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US"),{"Index", "Letter"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Name"),
#"Sorted Rows" = Table.Sort(#"Merged Columns",{{"Object", Order.Ascending}})
in #"Sorted Rows"

enter image description here

Upvotes: 3

Related Questions