Pivoting error in power query

[Anonymous]

Hi Experts,

How can we achieve below pivoting in Power Query or close to this?

Please help?

Getting below error due to duplicate records for User 1, but values are different fpr this user.

Please help

[grandtotal · Accepted solution]

You can try List.Distinct function as aggregation (not available through the UI):

let
   Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABKOjiCWUqwOspgTkDBCEwOpMwGLGaHrjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Template = _t, Title = _t, Value = _t]),
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
   #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Title]), "Title", "Value", List.Distinct),
   #"Expanded AA" = Table.ExpandListColumn(#"Pivoted Column", "AA"),
   #"Expanded AB" = Table.ExpandListColumn(#"Expanded AA", "AB")
in
   #"Expanded AB"

Not perfect but maybe you can use it.

[Anonymous]

Aggeregation is not required , I need data in seperate rows for User 1

[Idrissshatila]

Hello,

you Should click on both columns title and value => then you click on the transforming tab => then click on pivot column and you should see the result as per the screenshot below

If I answered your question, please mark my post as solution, Appreciate your Kudos!