Forum Discussion

obriaincian's avatar
obriaincian
Icon for Resolver I rankResolver I
2 years ago

Unpivoting and re-Pivoting creating duplicates

Hi All,   I have the below table in Power BI    Index  Manager First Name    Manager Last Name   HC Aug-23        HC Sep-23      HC Oct-23     HC Nov-23      HC Dec-23        HC Jan-24...
Need Help
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

show next steps

obriaincian's avatar
obriaincian
Icon for Resolver I rankResolver I
2 years ago

Ahmedx 

 

unfortunately i can't show all the columns but these snippets are from the final step 

 

This column shows the sum

 

Below are the columns that need to be summed, If we take the first row above which returns 13 for the sum, we can see below that the sum should be zero, I think what's happening is that it's summing all columns with a value as I have a column called "FY NU" which has 12 and the index column for this row is 1 which gives us 13

 

 

 

 

 

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    pls try this

    List.Sum( List.Transform(
List.Range( Record.FieldValues(_),
MGoM[Index]{0},MGoM[Index]{ List.Count(MGoM[Index])-1}),Number.From))

     

    • obriaincian's avatar
      obriaincian
      Icon for Resolver I rankResolver I
      2 years ago

      Ahmedx Thank you so much for all your efforts but unfortunately this doesn't work either, not sure why 

      • Ahmedx's avatar
        Ahmedx
        Icon for Super User rankSuper User
        2 years ago

        you can only give two lines to see what the problem is