Power query group by/merge question

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
142
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
try

VendorOrder RefCreation DateTotal PO AmountPO CurrencyDocumentPayment AmountPayment Date
1000010034​
B1111111
24/12/2018​
191.78​
USD
5100006334​
203.84​
17/01/2019​
1000010034​
B1111111
24/12/2018​
191.78​
USD
0​
0​
1000010034​
C1111111
04/02/2019​
400​
USD
0​
0​
Result
VendorOrder RefCreation DateTotal PO AmountPO CurrencyDocumentPayment AmountPayment Date
1000010034​
B1111111
24/12/2018​
191.78​
USD
5100006334​
203.84​
17/01/2019​
1000010034​
C1111111
04/02/2019​
400​
USD
0​
0​

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
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

Ok will do, thanks Sandy.

What do you mean by M-code is not optimised?
 
Upvote 0
What do you mean by M-code is not optimised?

because maybe there exist a much better solution, more sophisticated

and please don't quote whole post, use Reply instead of Reply With Quote
 
Last edited:
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
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

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

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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