Power query group by/merge question

D

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
167
Office Version
  1. 365
Platform
  1. Windows

Book1
ABCDEFGH
1VendorOrder RefCreation DateTotal PO AmountPO CurrencyDocumentPayment AmountPayment Date
21000010034B111111112/24/2018191.78USD5100006334203.841/17/2019
31000010034B111111112/24/2018191.78USD00.00
41000010034C11111112/4/2019400.00USD00.00
5
6Desired output:
7
8VendorOrder RefCreation DateTotal PO AmountPO CurrencyDocumentPayment AmountPayment Date
91000010034B111111112/24/2018191.78USD5100006334203.841/17/2019
101000010034C11111112/4/2019400.00USD00.00
Sheet1
<p style="width:8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3 (2)</p><br /><br />

How can I reduce the first table into the desired output using Power Query?
Not sure how to deal with the zeroes.
Line 3 is created when I book the invoice
Line 2 is created when the invoice is paid
So in essence, for that particular invoice B1111111, I only need to see Line 2 in the output
 
Sort by date Sort by votes
lrobbo314 said:
post#6
Click to expand...

I suggest to check your M-code for this source data:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Vendor[/td][td=bgcolor:#5B9BD5]Order Ref[/td][td=bgcolor:#5B9BD5]Creation Date[/td][td=bgcolor:#5B9BD5]Total PO Amount[/td][td=bgcolor:#5B9BD5]PO Currency[/td][td=bgcolor:#5B9BD5]Document[/td][td=bgcolor:#5B9BD5]Payment Amount[/td][td=bgcolor:#5B9BD5]Payment Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1000010034​
[/td][td=bgcolor:#DDEBF7]B1111111[/td][td=bgcolor:#DDEBF7]
24/12/2018​
[/td][td=bgcolor:#DDEBF7]
191.78​
[/td][td=bgcolor:#DDEBF7]USD[/td][td=bgcolor:#DDEBF7]
5100006334​
[/td][td=bgcolor:#DDEBF7]
203.84​
[/td][td=bgcolor:#DDEBF7]
17/01/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1000010034​
[/td][td]B1111111[/td][td]
24/12/2018​
[/td][td]
191.78​
[/td][td]USD[/td][td]
0​
[/td][td]
0​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1000010034​
[/td][td=bgcolor:#DDEBF7]C1111111[/td][td=bgcolor:#DDEBF7]
04/02/2019​
[/td][td=bgcolor:#DDEBF7]
400​
[/td][td=bgcolor:#DDEBF7]USD[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1000010035​
[/td][td]D222[/td][td]
01/05/2019​
[/td][td]
222.55​
[/td][td]USD[/td][td]
666667​
[/td][td]
543.8​
[/td][td]
01/06/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1000010035​
[/td][td=bgcolor:#DDEBF7]D222[/td][td=bgcolor:#DDEBF7]
01/05/2019​
[/td][td=bgcolor:#DDEBF7]
222.55​
[/td][td=bgcolor:#DDEBF7]USD[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1000010035​
[/td][td]D222[/td][td]
10/10/2019​
[/td][td]
123​
[/td][td]USD[/td][td]
0​
[/td][td]
0​
[/td][td][/td][/tr]
[/table]

result is:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Vendor[/td][td=bgcolor:#70AD47]Order Ref[/td][td=bgcolor:#70AD47]Creation Date[/td][td=bgcolor:#70AD47]Total PO Amount[/td][td=bgcolor:#70AD47]PO Currency[/td][td=bgcolor:#70AD47]Document[/td][td=bgcolor:#70AD47]Payment Amount[/td][td=bgcolor:#70AD47]Payment Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1000010034​
[/td][td=bgcolor:#E2EFDA]B1111111[/td][td=bgcolor:#E2EFDA]
24/12/2018​
[/td][td=bgcolor:#E2EFDA]
191.78​
[/td][td=bgcolor:#E2EFDA]USD[/td][td=bgcolor:#E2EFDA]
5100006334​
[/td][td=bgcolor:#E2EFDA]
203.84​
[/td][td=bgcolor:#E2EFDA]
17/01/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1000010034​
[/td][td]C1111111[/td][td]
04/02/2019​
[/td][td]
400​
[/td][td]USD[/td][td]
0​
[/td][td]
0​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1000010035​
[/td][td=bgcolor:#E2EFDA]D222[/td][td=bgcolor:#E2EFDA]
01/05/2019​
[/td][td=bgcolor:#E2EFDA]
222.55​
[/td][td=bgcolor:#E2EFDA]USD[/td][td=bgcolor:#E2EFDA]
666667​
[/td][td=bgcolor:#E2EFDA]
543.8​
[/td][td=bgcolor:#E2EFDA]
01/06/2019​
[/td][/tr]
[/table]

but IMHO should be:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Vendor[/td][td=bgcolor:#70AD47]Order Ref[/td][td=bgcolor:#70AD47]Creation Date[/td][td=bgcolor:#70AD47]Total PO Amount[/td][td=bgcolor:#70AD47]PO Currency[/td][td=bgcolor:#70AD47]Document[/td][td=bgcolor:#70AD47]Payment Amount[/td][td=bgcolor:#70AD47]Payment Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1000010034​
[/td][td=bgcolor:#E2EFDA]B1111111[/td][td=bgcolor:#E2EFDA]
24/12/2018​
[/td][td=bgcolor:#E2EFDA]
191.78​
[/td][td=bgcolor:#E2EFDA]USD[/td][td=bgcolor:#E2EFDA]
5100006334​
[/td][td=bgcolor:#E2EFDA]
203.84​
[/td][td=bgcolor:#E2EFDA]
17/01/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1000010034​
[/td][td]C1111111[/td][td]
04/02/2019​
[/td][td]
400​
[/td][td]USD[/td][td]
0​
[/td][td]
0​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1000010035​
[/td][td=bgcolor:#E2EFDA]D222[/td][td=bgcolor:#E2EFDA]
01/05/2019​
[/td][td=bgcolor:#E2EFDA]
222.55​
[/td][td=bgcolor:#E2EFDA]USD[/td][td=bgcolor:#E2EFDA]
666667​
[/td][td=bgcolor:#E2EFDA]
543.8​
[/td][td=bgcolor:#E2EFDA]
01/06/2019​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1000010035​
[/td][td]D222[/td][td]
10/10/2019​
[/td][td]
123​
[/td][td]USD[/td][td]
0​
[/td][td]
0​
[/td][td][/td][/tr]
[/table]
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Yeah, the OP was confusing to me and I'm not really sure if my code is correct or not. It was too small of a sample data set and desired results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
90
MeaclH
M
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
M
  • Question Question
Parent/Child visual help
Replies
1
Views
326
Peter_SSs
Peter_SSs
P
  • Solved
I can't figure out how to get the fields to populate from the Invoice file..
Replies
6
Views
322
Herakles
H
alm395
  • Question Question
How do you perform a calculation based on status entry?
Replies
2
Views
737
KRice
K

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top