Create report in desired format

[tangutoori]

Dear Team,

i have an excel with data in the format(Rows and colums) as shown in table-1.

i want table-1 data to be formateed to table-2 format as shown below using power bi.

Logic for implementation

For each S.no there may be one employee or many employees engaged.

We have to consider Co employee also as an employee.

For S.no(11718) have 4 employees engaged then 5000 points should be divided among them equally i.e. 5000/4=1250 for each employee.
For S.no(11135) have 1 employee engaged then he will get total 5000 points.

[v-yuta-msft]

@tangutoori ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@tangutoori,

---

 Logic for implementation

For each S.no there may be one employee or many employees engaged.

We have to consider Co employee also as an employee.

For S.no(11718) have 4 employees engaged then 5000 points should be divided among them equally i.e. 5000/4=1250 for each employee.
For S.no(11135) have 1 employee engaged then he will get total 5000 points.

---

To be general, table transformation can be done by clicking query editor-> Transform-> Pivot and Unpivot, then use List.Average() to calculate average value.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.