How to merge rows (Date format) with power query

[Sian1]

Hi how can i merge rows and columns from

ID	Pending Date	Reason	Comments
R-001A1WO	1/27/2022​	Policy Document Review	Draft sent to local contact for review and signature
R-001A1WO	1/21/2022​	Policy Document Review	policy draft sent to local client for review and acceptance
R-001A1WO	1/18/2022​	Policy Document Review	draft sent, waiting for approval
R-001A1YO	9/2/2021​	Policy Document Review	questions to PO sent via mail
R-001A1YO	2/18/2022​	Policy Document Review	questions to PO send via mail
R-001A1YO	2/23/2022​	Terms & Conditions Review	Pending issuance order form local broker
R-001A1ZS	2/23/2022​	Policy Document Review	Draft sent to the local broker for approval
R-001A203	1/31/2022​	Policy Document Review	policy draft sent to local broker for review and acceptance
R-001A206	1/11/2022​	Terms & Conditions Review	Pending issuance order from local broker
R-001A206	1/11/2022​	Terms & Conditions Review	Pending issuance order from local broker
R-001A20C	1/16/2022​	KYC / AML Forms	An email sent to the broker that we need to open a new account code in our systems, so please provide the KYC and others under the same list/ requirements, in addition to payment request (proforma invoice) due to back dated policy.
R-001A21Q	1/25/2022​	Other	premium outstanding. •BNCOO210000000001037 - 988.121 •BNCOO210000000002432 - 6 686.801 •BNCOO210000000002437 - 1 055.439
R-001A21Q	2/10/2022​	KYC / AML Forms	Still waiting for the contact number
R-001A21W	12/1/2021​	Other	Waiting for issuance order.
R-001A21X	2/7/2022​	Other	Policy cannot be issued until previous premium is payed.
R-001A229	2/14/2022​	Other	Upfront premium requested from local AON broker because inception is older than 5 months - also missing the client bank details letter as it was not provided along with the ID of authorized signatory
R-001A22D	11/8/2021​	Policy Document Review	policy draft sent to PO for confrimation, questions to PO are in the draft
R-001A22K	9/2/2021​	Policy Document Review
R-001A22K	2/7/2022​	Policy Document Review

to this;

ID	Pending Date	Reason; Comments
R-001A1WO	1/27/2022; 1/21/2022; 1/18/2022;	Policy Document Review; Draft sent to local contact for review and signature Policy Document Review; policy draft sent to local client for review and acceptance Policy Document Review; draft sent, waiting for approval

appreciate any helps

 

[johnny51981]

Give these steps a try.

First merges the Reason and Comments together in one column, delimiting it by a semi colon.
Then a Group By, but instead of a List.Sum, you are doing a Text.Combine, which isn't available in the walk through window when you click the Group By button.

#"Merged Columns" = Table.CombineColumns(PREVIOUS STEP,{"Reason", "Comments"},Combiner.CombineTextByDelimiter("; ", QuoteStyle.None),"Reason; Comments"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"ID"}, {{"Pending Date", each Text.Combine([Pending Date],"; ")},{"Reason; Comments", each Text.Combine([Reason; Comments]," ")}}

 

[Sian1]

thanks, however i got this error message

Give these steps a try.

First merges the Reason and Comments together in one column, delimiting it by a semi colon.
Then a Group By, but instead of a List.Sum, you are doing a Text.Combine, which isn't available in the walk through window when you click the Group By button.

#"Merged Columns" = Table.CombineColumns(PREVIOUS STEP,{"Reason", "Comments"},Combiner.CombineTextByDelimiter("; ", QuoteStyle.None),"Reason; Comments"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"ID"}, {{"Pending Date", each Text.Combine([Pending Date],"; ")},{"Reason; Comments", each Text.Combine([Reason; Comments]," ")}}

Hi thanks, however i still get the error message;

 

[johnny51981]

You have it as List.Combine for your “Pending Date”, it is supposed to be Text.Combine

 

[citizenbh]

Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Pending Date", type text}, {"Reason", type text}, {"Comments", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Reason", "Comments"},Combiner.CombineTextByDelimiter("; ", QuoteStyle.None),"Reason_Comments"),
    GroupedRows=Table.Group(#"Merged Columns", {"ID"}, {{"Pending Date", each Text.Combine([Pending Date], Character.FromNumber(10))},{"Reason_Comments", each Text.Combine([Reason_Comments], Character.FromNumber(10))}})
in
    GroupedRows