Help with a table transformation

[julianst24]

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

[v-cazheng-msft]

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]

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 · Accepted solution]

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]

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

[Tutu_in_YYC]

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