Pivot to add lots of index columns

[Isambard]

Hi,

I have a table of categories in column1, and values in column2. There are hundreds of categories but only about 5 values, which are the same across every category. I've indexed the values by merging a sort column with values 1-5 depending on the value in column2.

I'd like to pivot the table out so my headings are [category1value], [category1index], [category2value], [category2index], etc however I'm struggling to make this work. Pivoting seems to give an error try to sum or count the values, and when set to "don't agregate is throwing up an error, "Expression.Error: There were too many elements in the enumeration to complete the operation.
Details"

Thanks for any help.

[v-nmadadi-msft]

Hi @Isambard 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[Omid_Motamedise]

We can achieve this with grouping, adding index per group, and then pivoting twice. So, first a Group the data by Category, then add an index to each group (result table) then expand the table and pivot them as paired columns.

[wardy912]

Hi @Isambard 

First, add a custom column to use as a unique identifier

[Category] & [Value]

Then select the value and index columns and unpivot

Add another custom column

[Category] & [Attribute]

Remove the columns "Category", "Custom", "Attribute" and unpivot on the custom.1 column

I hope this helps! Please give a thumbs up and mark as solved if it does, thanks!

[SundarRaj]

Hi @Isambard ,

Please put out the sample data and the output data you want. Thanks

[Isambard]

Thanks,

Current format is:

Category1	A	1
Category1	B	2
Category1	C	3
Category2	A	1
Category2	A	1
Category2	C	3
...

I would like to rearange it like:

Category1Value	Category1Index	Category2Value	Category2Index
A	1	A	1
B	2	A	1
C	3	C	3

[SundarRaj]

Hi @Isambard ,

Here's a dynamic PQ solution with a dynamic column headers solution. I'll attach the file link for your reference. I'll also the attach the images and the M code used. Thanks

File Link:
https://docs.google.com/spreadsheets/d/1Q67CS_Va2MNc7KqmTroQDoIhAq-Ikzoc/edit?usp=sharing&ouid=10475...

Here's the code:
let
Source = #table(
{"Category", "Subcategory", "Value"},
{
{"Category1", "A", 1},
{"Category1", "B", 2},
{"Category1", "C", 3},
{"Category2", "A", 1},
{"Category2", "A", 1},
{"Category2", "C", 3}
}
),
Group = Table.Group(
Source,
{"Category"},
{
{
"Group",
each _[[Subcategory], [Value]],
type table [Category = text, Subcategory = text, Value = number]
}
}
),
FromCols = Table.TransformColumns(Group, {"Group", each Table.FromColumns(Table.ToRows(_))}),
Expand = Table.ExpandTableColumn(
FromCols,
"Group",
{"Column1", "Column2", "Column3"},
{"Column1", "Column2", "Column3"}
),
Transpose = Table.PromoteHeaders(Table.Transpose(Expand)),
DynamicColNames = List.Combine(
List.Transform(List.Distinct(Source[Category]), each {_, _ & "Index"})
),
Custom1 = Table.RenameColumns(
Transpose,
List.Zip({Table.ColumnNames(Transpose), DynamicColNames})
)
in
Custom1