Forum Discussion

julianst24's avatar
julianst24
Frequent Visitor
4 years ago
Solved

Help with a table transformation

Hi all, ive created a table with some rankings from another table, the thing is that i cannot group it in "transform data" as its manually created by some rules in PBI, but i need this table to occup only 1 row and im getting it into 3 differents. how can i force it to be in only one row? in this way i can connect this table to another for a 1-1 relation and subsequent logics...

Thanks in advance

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi julianst24,

     

    Please make a little bit change to the earlier formula.

    final table =
SUMMARIZE (
    Data,
    Data[serial],
    "RANKING1", MAX ( Data[RANKING1] ),
    "RANKING2", MAX ( Data[RANKING2] ),
    "RANKING3", MAX ( Data[RANKING3] ),
    "START DATE RANKING1", MAX ( Data[START DATE RANK1] ),
    "START DATE RANKING2", MAX ( Data[START DATE RANK2] ),
    "START DATE RANKING3", MAX ( Data[START DATE RANK3] ),
    "END DATE RANKING1", MAX ( Data[END DATE RANK1] ),
    "END DATE RANKING2", MAX ( Data[END DATE RANK2] ),
    "END DATE RANKING3", MAX ( Data[END DATE RANK3] )
)

     

    Then, the Calculated Table will look like this.

     

    Also, attach the sample pbix as reference.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

5 Replies

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Super User
    4 years ago

    These type of transformations are best done in Power Query. Do you have access to power query?

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    4 years ago

    Hi julianst24,

     

    Please make some changes to your formula.

    final table =
SUMMARIZE (
    Data,
    "serial", MAX ( Data[serial] ),
    "RANKING1", MAX ( Data[RANKING1] ),
    "RANKING2", MAX ( Data[RANKING2] ),
    "RANKING3", MAX ( Data[RANKING3] ),
    "START DATE RANKING1", MAX ( Data[START DATE RANK1] ),
    "START DATE RANKING2", MAX ( Data[START DATE RANK2] ),
    "START DATE RANKING3", MAX ( Data[START DATE RANK3] ),
    "END DATE RANKING1", MAX ( Data[END DATE RANK1] ),
    "END DATE RANKING2", MAX ( Data[END DATE RANK2] ),
    "END DATE RANKING3", MAX ( Data[END DATE RANK3] )
)

     

    Then, the Calculated Table will look like this.

     

    Also, attach the sample pbix as reference.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

    • julianst24's avatar
      julianst24
      Frequent Visitor
      4 years ago

      Hey! thank you very much, although i have some issue, the data finnally has a lot more Serials in it so the MAX function gives me only 1 SERIAL while i would need this applied to every Serial in DATA Table:

      this is a sample of the data, as you see, there are more than 1 serial and they are also repetead...how can i change the code to bring each "MAX" occurence of every serial??

      Thanks a lot!!

      • v-cazheng-msft's avatar
        v-cazheng-msft
        Community Support
        4 years ago

        Hi julianst24,

         

        Please make a little bit change to the earlier formula.

        final table =
SUMMARIZE (
    Data,
    Data[serial],
    "RANKING1", MAX ( Data[RANKING1] ),
    "RANKING2", MAX ( Data[RANKING2] ),
    "RANKING3", MAX ( Data[RANKING3] ),
    "START DATE RANKING1", MAX ( Data[START DATE RANK1] ),
    "START DATE RANKING2", MAX ( Data[START DATE RANK2] ),
    "START DATE RANKING3", MAX ( Data[START DATE RANK3] ),
    "END DATE RANKING1", MAX ( Data[END DATE RANK1] ),
    "END DATE RANKING2", MAX ( Data[END DATE RANK2] ),
    "END DATE RANKING3", MAX ( Data[END DATE RANK3] )
)

         

        Then, the Calculated Table will look like this.

         

        Also, attach the sample pbix as reference.

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

         

        Best Regards,

        Community Support Team _ Caiyun