Power query group by/merge question

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
113
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Vendor</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Order Ref</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Creation Date</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Total PO Amount</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">PO Currency</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Document</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Payment Amount</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Payment Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">1000010034</td><td style="border-top: 1px solid black;background-color: #FFC000;;">B1111111</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">12/24/2018</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">191.78</td><td style="border-top: 1px solid black;background-color: #FFC000;;">USD</td><td style="border-top: 1px solid black;background-color: #FFC000;;">5100006334</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">203.84</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">1/17/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;background-color: #FFC000;;">1000010034</td><td style="background-color: #FFC000;;">B1111111</td><td style="text-align: right;background-color: #FFC000;;">12/24/2018</td><td style="text-align: right;background-color: #FFC000;;">191.78</td><td style="background-color: #FFC000;;">USD</td><td style="text-align: right;background-color: #FFC000;;">0</td><td style="text-align: right;background-color: #FFC000;;">0.00</td><td style="text-align: right;background-color: #FFC000;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;background-color: #FFC000;;">1000010034</td><td style="background-color: #FFC000;;">C1111111</td><td style="text-align: right;background-color: #FFC000;;">2/4/2019</td><td style="text-align: right;background-color: #FFC000;;">400.00</td><td style="background-color: #FFC000;;">USD</td><td style="text-align: right;background-color: #FFC000;;">0</td><td style="text-align: right;background-color: #FFC000;;">0.00</td><td style="text-align: right;background-color: #FFC000;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Desired output:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Vendor</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Order Ref</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Creation Date</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Total PO Amount</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">PO Currency</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Document</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Payment Amount</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #000000;;">Payment Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">1000010034</td><td style="border-top: 1px solid black;background-color: #FFC000;;">B1111111</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">12/24/2018</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">191.78</td><td style="border-top: 1px solid black;background-color: #FFC000;;">USD</td><td style="border-top: 1px solid black;background-color: #FFC000;;">5100006334</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">203.84</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFC000;;">1/17/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;background-color: #FFC000;;">1000010034</td><td style="background-color: #FFC000;;">C1111111</td><td style="text-align: right;background-color: #FFC000;;">2/4/2019</td><td style="text-align: right;background-color: #FFC000;;">400.00</td><td style="background-color: #FFC000;;">USD</td><td style="text-align: right;background-color: #FFC000;;">0</td><td style="text-align: right;background-color: #FFC000;;">0.00</td><td style="text-align: right;background-color: #FFC000;;"></td></tr></tbody></table><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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,314
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:

danhendo888

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

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,314
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:

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,647
Office Version
365, 2019, 2016
Platform
Windows
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
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,647
Office Version
365, 2019, 2016
Platform
Windows
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,314
last one (post#6) is very nice (y)
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,647
Office Version
365, 2019, 2016
Platform
Windows
Thanks, buddy!
 

danhendo888

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,647
Office Version
365, 2019, 2016
Platform
Windows
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.
 

Forum statistics

Threads
1,086,116
Messages
5,387,921
Members
402,088
Latest member
poppa57

Some videos you may like

This Week's Hot Topics

Top