combine data

[melina]

Hi All,

I have a table in CSV file. The table in CSV file will upload to PBI. The table as below :

Purch.Doc.	Vendor Name	Scheduled Qty
554321	MyApps SDN BHD	120
554321	MyApps SDN BHD	225
554322	Abu Munawer SDN BHD	1000
554322	Abu Munawer SDN BHD	1640

My question can PBI combine the rows that have same Purch.Doc number?

Then sum the total for Schedule Qty that have same Purch.Doc?

At the end, the table display like this :

Purch.Doc.	Vendor Name	Scheduled Qty
554321	MyApps SDN BHD	345
554322	Abu Munawer SDN BHD	2640

[MarcelBeug]

In "Edit Queries", go to the "Transform" tab, select your first 2 columns, choose  "Group By" and fill out the fields, such that the quantities will be summed.

[melina]

Thank for your reply, but when i try to use Group By method, the vendor name column missing. I need to display vendor name, Why?

[MarcelBeug]

Probably you didn't select both the Purch.Doc. and the Vendor Name columns before you chose "Group By".

[melina]

oh, you are correct. Ok after i already select, what input should i put in the box?

How the query will know for the same Purch.Doc?

[MarcelBeug · Accepted solution]

The relevant information is outside your screenshot.

Below just an arbitrary example with grouping on 2 fields which must be under the "Goup By" section of the screen.

[melina]

Thanks a lot for helping me. Now i got the idea and solution to solve this problem..tq!