Combine rows if they meet certain Critiria

[Anonymous]

Im trying to match out entries in one of our GL accounts, and need to add a reciving number for each.

THerefore I need to clean the receiving data.

 

PO no	po line	Recvng no	item	trans date	Report qty
MT19469001	1	515001	MP-5H	101519	20
MT19469001	2	327001	PT-8	091019	10
MT19469001	3	515002	MC-3A HONDA	101519	10
MT19469001	3	515002	MC-3A HONDA	101519	10
MT19469001	5	1308001	AP-21	031220	1
MT19469001	5	1308002	AP-21	031220	1
MT19469001	5	1308003	AP-21	031220	1
MT19469001	5	1308004	AP-21	031220	1
MT19469001	5	1308005	AP-21	031220	1
MT19469001	5	1308006	AP-21	031220	1
MT19469001	5	1308007	AP-21	031220	1
MT19469001	5	1308008	AP-21	031220	1
MT19469001	5	1308009	AP-21	031220	1
MT19469001	5	1308010	AP-21	031220	1
MT19469001	15	746003	ST541A02	112119	1
MT19469001	15	746004	ST541A02	112119	1
MT19469001	15	912001	ST541A02	121819	1
MT19469001	15	912002	ST541A02	121819	1
MT19469001	15	912003	ST541A02	121819	1
MT19490001	1	72001	WIDE DIE	071619	52
MT19500001	1	3522001	RB 2214 TM	051821	1
MT19579001	1	795001	AP-3	120419	1
MT19579001	1	795002	AP-3	120419	1
MT19588001	1	651001	64752	110619	1

To do so I need the following done.:

if the PO number, line number, and transaction date match on multiple rows AND the report qty is 1 for each row

Then excess rows need to be summed into the the row with the lowest reciving number and excess rows deleted.

These are serielaized items, and they get individual receiving numbers,

but are all received at the same time. So all we need is the total for the PO line for that day and the first receiving number.

PO no	po line	Recvng no	item	trans date	Report qty
MT19469001	1	515001	MP-5H	101519	20
MT19469001	2	327001	PT-8	091019	10
MT19469001	3	515002	MC-3A HONDA	101519	10
MT19469001	3	515002	MC-3A HONDA	101519	10
MT19469001	5	1308001	AP-21	031220	10
MT19469001	15	746003	ST541A02	112119	5
MT19490001	1	72001	WIDE DIE	071619	52
MT19500001	1	3522001	RB 2214 TM	051821	1
MT19579001	1	795001	AP-3	120419	2
MT19588001	1	651001	64752	110619	1

 In the below you will notice that the dates do not match therefore I did not manually combine them into 1 line.

Please note I do not know dax, I am a beginer but i follow steps very well 🙂

[Anonymous]

Hi @Anonymous ,

How is the situation now? If my answer solved the problem, please accept it as a solution, which will be seen by more people in need.

Best regards,

Mengmeng Li

[Anonymous]

Hi @Anonymous ,

I don't quite understand what you mean, please provide some examples.

Best regards,

Mengmeng Li

[Anonymous]

Hi @Anonymous ,

Based on your description, you want to group rows with "qty" greater than 1 by: PO number, line number, and transaction date.

Here is my test for your reference.

Original table.

Expected output.

Calculated Table with DAX.

Table 2 = 
VAR calculateTable01 = CALCULATETABLE(SUMMARIZE(
    'Table',
    'Table'[PO no],
    'Table'[po line],
	'Table'[trans date ],"Report qty",SUM('Table'[Report qty]),"item",MAX('Table'[item]),"Recvng no",MAX('Table'[Recvng no])),'Table'[Report qty]=1)
VAR calculateTable02 = SELECTCOLUMNS(calculateTable01,"PO no",[PO no],"po line",[po line],"Recvng no",[Recvng no],"item",[item],"trans date",[trans date ],"Report qty",[Report qty])
RETURN UNION(FILTER('Table','Table'[Report qty]>1),calculateTable02)

Best regards,

Mengmeng Li

[Anonymous]

Is there a way to make this show the first/lowest receiving number on the combined line? this one shows the highest. That way when it is researched in the system they will see the other numbers if needed

[lbendlin]

You shared from a personal SharePoint/Onedrive.  You may not want to do that.  

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

ty