cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
melina Member
Member

combine data

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 NameScheduled Qty
554321MyApps SDN BHD120
554321MyApps SDN BHD225
554322Abu Munawer SDN BHD1000
554322Abu Munawer SDN BHD1640

 

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 NameScheduled Qty
554321MyApps SDN BHD345
554322Abu Munawer SDN BHD2640
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: combine data

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.

 

Group By 2.png

Specializing in Power Query Formula Language (M)
6 REPLIES 6
Super User
Super User

Re: combine data

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.

Specializing in Power Query Formula Language (M)
melina Member
Member

Re: combine data

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?

Super User
Super User

Re: combine data

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

Specializing in Power Query Formula Language (M)
melina Member
Member

Re: combine data

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?

Capture.PNG

Super User
Super User

Re: combine data

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.

 

Group By 2.png

Specializing in Power Query Formula Language (M)
melina Member
Member

Re: combine data

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