Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Percentages shows up as null while pivoting

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?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.