Percentages shows up as null while pivoting

[Anonymous]

Hi!

In data I have percentages of each training type, and when I do pivot some percentages turns out as null even if there was 100% before pivoting. 

It doesn't show any error, just turns some percentages to null. 

How I could fix it?

[Anonymous]

Hi @Anonymous 

Can you provide some sample picture to display the problem you have faced, based on the information you offered above, the problem cannot be reproduced.

Best Regards!

Yolo Zhu

[Anonymous]

Sure.

Here for example how it looks before pivot:

And here is after:

#"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[#"Training Type"]), "Training Type", "Compliance %", List.Sum)

[Anonymous · Accepted solution]

Hi @Anonymous 

You need to add index group by the Training Type first, you can put the following code to advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk5MSy2pVNJRMlSK1YlWcnMPgLMDSxNzMtMykxNLMvPz4KLkazBA0mCAVYOBngUZdsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Training Type" = _t, #"Compliance %" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Training Type", type text}, {"Compliance %", Percentage.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Training Type"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Compliance %", "Index"}, {"Compliance %", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Compliance %", Percentage.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"Training Type"]), "Training Type", "Compliance %", List.Sum)
in
    #"Pivoted Column"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.