Forum Discussion
Pivot columns returns errors and duplicates rows
Hi,
I appreciate if anyone could help.
I am trying to pivot a dataset but PQ returns errors. I did try to insert an index column, but it didn't seem to help. Maybe I added it wrong.
This is the table I have:
What I want to achieve by pivoting the columns.
Dataset with the steps I did:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZXRboIwFIZfpeF2im2lgJeoxJBF1wjuxnlBXFWyWRZgxsffqZkmyhYM2rGrc5o0/fK1f9v53KAd6nYopl2jZQRyhzgn0DmYEAyVMuJCUa2//xAyF9AN0q1q4yJJJYIxT/MikWvo+rMwmPhhiGaTIArV1MhYtO4MGfrcm0ZjfxLBYPwYjZB1BaRLLNNht7iEng5MUzbe87+woWplm7qm1pjVotRzcVzT1pmyOpBmTKoS9icmlqpuj92mUpWvWpR6LoQQs6f1HauHacqmKmX6bEYY4+7h4DFm5HtxbBJ2xvD3y00s1wINk9VKZEIuRX5u9B7Lt1O9CtK+D8U+QjgP2sDpWWrgMHr4jonDcGnHvNedyIokh0XRyyfG1EY8S7ep2sEL4K93SCu2dHCNuJ6uWAOupMNK4QHO4R5YP/2AfUgEGmzibC3yIzGQBaQoL6rexacp1w09sxw9wM4uvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [postingDate = _t, documentNumber = _t, accountNumber = _t, debitAmount = _t, creditAmount = _t, account.category = _t, account.subCategory = _t, account.accountType = _t, dimensionSetLines.displayName = _t, dimensionSetLines.valueCode = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"postingDate", type date}, {"documentNumber", type text}, {"accountNumber", Int64.Type}, {"debitAmount", type number}, {"creditAmount", type number}, {"account.category", type text}, {"account.subCategory", type text}, {"account.accountType", type text}, {"dimensionSetLines.displayName", type text}, {"dimensionSetLines.valueCode", type text}}),
#"Grouped Rows1" = Table.Group(#"Changed Type", {"postingDate", "documentNumber", "accountNumber", "debitAmount", "creditAmount", "account.category", "account.subCategory", "account.accountType", "dimensionSetLines.displayName"}, {{"Dep2", each _, type table [postingDate=nullable date, documentNumber=nullable text, accountNumber=nullable number, debitAmount=nullable number, creditAmount=nullable number, account.category=nullable text, account.subCategory=nullable text, account.accountType=nullable text, dimensionSetLines.displayName=nullable text, dimensionSetLines.valueCode=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Index", each Table.AddIndexColumn([Dep2],"Index",1)),
#"Expanded Index" = Table.ExpandTableColumn(#"Added Custom", "Index", {"dimensionSetLines.valueCode", "Index"}, {"Index.dimensionSetLines.valueCode", "Index.Index"}),
#"Removed Columns5" = Table.RemoveColumns(#"Expanded Index",{"Dep2"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Columns5", List.Distinct(#"Removed Columns5"[dimensionSetLines.displayName]), "dimensionSetLines.displayName", "Index.dimensionSetLines.valueCode")
in
#"Pivoted Column1"
Kind regards,
Zosy
This one took a bit of working out.
The code you have posted works (i.e. no errors) but doesn't give the result you want. I think this has confused people, it confused me) because I was expecting code with the error.
Anyway, the code posted is your attempt to fix the problem and I think you have the right approach.
However after the 'Index Expanded' step, the data types on the 2 last columns are 'Any' and this prevents the Pivot from working in the way you want.
So assign the correct data type on those two last columns and I think it will work.
I haven't checked it too closely so you'll still need to do your own testing.
Let me know how it goes
4 Replies
I did look to see what was wrong and the started searching online how could I fix it. In the majority of cases people fixed it by adding an Index column and Group by function. I tried it too but it did not return what I need.
This one took a bit of working out.
The code you have posted works (i.e. no errors) but doesn't give the result you want. I think this has confused people, it confused me) because I was expecting code with the error.
Anyway, the code posted is your attempt to fix the problem and I think you have the right approach.
However after the 'Index Expanded' step, the data types on the 2 last columns are 'Any' and this prevents the Pivot from working in the way you want.
So assign the correct data type on those two last columns and I think it will work.
I haven't checked it too closely so you'll still need to do your own testing.
Let me know how it goes
Apologies for the confusion. Indeed it was just the data type for the index column. I really apperciate your help.
