Forum Discussion
Solved
Creating new column by index
Hello everyone,
I need help with manipulation in table.
I have a table:
| Product | Cat | Index |
| A | AA | 1 |
| A | AB | 2 |
| B | AA | 1 |
| C | AA | 1 |
| C | AB | 2 |
| C | AC | 3 |
And i have to manipulate with it, to get this:
| Product | 1 | 2 | 3 |
| A | AA | AB | |
| B | AA | ||
| C | AA | AB | AC |
Is this possible in power query?
Help guys
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIEEYZKsTpQrhOQMAJznVBlnbFwEYrBXBBhrBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Cat = _t, Index = _t]), #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Index]), "Index", "Cat"), #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"1", "2", "3"}) in #"Replaced Value"
2 Replies
Hi,
you should try Dynamic Column option in Power Query. You can find it in the Transform menu of Power Query Editor.
I hope the answer has helped you, if true please mark the answer as correct!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIEEYZKsTpQrhOQMAJznVBlnbFwEYrBXBBhrBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Cat = _t, Index = _t]), #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Index]), "Index", "Cat"), #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"1", "2", "3"}) in #"Replaced Value"
