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...
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

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

  • julianst24's avatar
    julianst24
    Frequent Visitor
    4 years ago

    Thank you very much for the solution, really appreciate it! have a great week 🙂