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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
try

[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][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Result[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[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]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"Order Ref", type text}, {"Creation Date", type date}, {"Total PO Amount", type number}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", type number}, {"Payment Date", type date}}),
    Group = Table.Group(Type, {"Vendor", "Order Ref", "Creation Date", "PO Currency"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(Group, "Total PO Amount", each List.First(List.Distinct(Table.Column([Count],"Total PO Amount")))),
    List2 = Table.AddColumn(List1, "Document", each List.First(List.Distinct(Table.Column([Count],"Document")))),
    List3 = Table.AddColumn(List2, "Payment Amount", each List.First(List.Distinct(Table.Column([Count],"Payment Amount")))),
    List4 = Table.AddColumn(List3, "Payment Date", each List.First(List.Distinct(Table.Column([Count],"Payment Date")))),
    ROC = Table.SelectColumns(List4,{"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}),
    TypeDate = Table.TransformColumnTypes(ROC,{{"Payment Date", type date}})
in
    TypeDate[/SIZE]

M-code is not optimised

edit:
btw. try to post your posts to the proper forum
 
Last edited:
Upvote 0
sandy666 said:
Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"Order Ref", type text}, {"Creation Date", type date}, {"Total PO Amount", type number}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", type number}, {"Payment Date", type date}}),
    Group = Table.Group(Type, {"Vendor", "Order Ref", "Creation Date", "PO Currency"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(Group, "Total PO Amount", each List.First(List.Distinct(Table.Column([Count],"Total PO Amount")))),
    List2 = Table.AddColumn(List1, "Document", each List.First(List.Distinct(Table.Column([Count],"Document")))),
    List3 = Table.AddColumn(List2, "Payment Amount", each List.First(List.Distinct(Table.Column([Count],"Payment Amount")))),
    List4 = Table.AddColumn(List3, "Payment Date", each List.First(List.Distinct(Table.Column([Count],"Payment Date")))),
    ROC = Table.SelectColumns(List4,{"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}),
    TypeDate = Table.TransformColumnTypes(ROC,{{"Payment Date", type date}})
in
    TypeDate[/SIZE]

M-code is not optimised

edit:
btw. try to post your posts to the proper forum
Click to expand...

Ok will do, thanks Sandy.

What do you mean by M-code is not optimised?
 
Upvote 0
Sandy, maybe you would know if this is more or less optimized than your solution, but here is a different way to get the same results.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Vendor", Int64.Type}, {"Order Ref", type text}, {"Creation Date", type date}, {"Total PO Amount", type number}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", type number}, {"Payment Date", type date}}),
    Group = Table.Group(Type, {"Order Ref"}, {{"Custom", each Table.First(_), type table}}),
    List = Table.AddColumn(Group, "Custom.1", each Record.ToList([Custom])),
    Extract = Table.TransformColumns(List, {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6", "Custom.1.7", "Custom.1.8"}),
    TypeII = Table.TransformColumnTypes(Split,{{"Custom.1.1", type number}, {"Custom.1.2", type text}, {"Custom.1.3", type date}, {"Custom.1.4", type number}, {"Custom.1.5", type text}, {"Custom.1.6", type number}, {"Custom.1.7", type number}, {"Custom.1.8", type date}}),
    Rename = Table.RenameColumns(TypeII,{{"Custom.1.1", "Vendor"}, {"Custom.1.3", "Creation Date"}, {"Custom.1.4", "Total PO Amount"}, {"Custom.1.5", "PO Currency"}, {"Custom.1.6", "Document"}, {"Custom.1.7", "Payment Amount"}, {"Custom.1.8", "Payment Date"}}),
    Remove = Table.RemoveColumns(Rename,{"Custom.1.2"}),
    Reorder = Table.ReorderColumns(Remove,{"Vendor", "Order Ref", "Custom", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"})
in
    Reorder
 
Upvote 0
Just found another way. I think this one is better than my last.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Group = Table.Group(Source, {"Order Ref"}, {{"Custom", each Table.First(_), type table}}),
    List = Group[Custom],
    ToTable = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}, {"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}),
    Type = Table.TransformColumnTypes(Expand,{{"Creation Date", type date}, {"Payment Date", type date}, {"Total PO Amount", Currency.Type}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", Currency.Type}})
in
    Type
 
Upvote 0
lrobbo314 said:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    [U]Group[/U] = Table.Group(Source, {"Order Ref"}, {{"Custom", [U]each Table.First(_), type table[/U]}}),
    List = Group[Custom],
    ToTable = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}, {"Vendor", "Order Ref", "Creation Date", "Total PO Amount", "PO Currency", "Document", "Payment Amount", "Payment Date"}),
    Type = Table.TransformColumnTypes(Expand,{{"Creation Date", type date}, {"Payment Date", type date}, {"Total PO Amount", Currency.Type}, {"PO Currency", type text}, {"Document", Int64.Type}, {"Payment Amount", Currency.Type}})
in
    Type
Click to expand...

Hello friends,
Can someone help me breakdown/read the following in the step called "Group":
each Table.First(_), type table

Thank you
 
Upvote 0
It’s grouping everything together based on the ‘Order Ref’ field, and the table.first function limits the resets to the first record from each Order Ref value. If you click on each step in the power query editor, you can see what is happening to the data. I manually edited the code to include the table.first function. Not sure if it is possible to do purely through the GUI.
 
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,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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