Pivot columns returns errors and duplicates rows

[Zosy]

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

[HotChilli · Accepted solution]

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

[Zosy]

Apologies for the confusion. Indeed it was just the data type for the index column. I really apperciate your help.

[rajulshah]

@Zosy 

If you will click on the error, you would get the error message where you may get an idea as what might be the issue.

[Zosy]

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.