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.

[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