Forum Discussion
Pivoting error in power query
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
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.
3 Replies
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.
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!
