How to Concatenate two columns with duplicated values

Question

Hi, I have a small problem that I can't really figure out how to solve.
I don't really know what to call this problem, but it's understandable below

I have a table that looks like this (as a CSV file with comma as delimiter):
ParentRecID, Deploy Issue
ID1, Process Issue
ID1, Timing Issue
ID2, Process Issue
ID3, Process Issue
ID4, Timing Issue
ID4, Technical Issue

And what I want it to look like, is like this:
ParentRecID, Deploy Issue
ID1, Process Issue | Timing Issue
ID2, Process Issue
ID3, Process Issue
ID4, Timing Issue | Technical Issue

How would I do this in Power query?

Answer 1

Hi @Victor Emil Søe Rasmussen,

Please try the following solution.

It will work starting from SQL Server 2017 onwards.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ParentRecID VARCHAR(5), DeployIssue VARCHAR(30));
INSERT @tbl (ParentRecID, DeployIssue) VALUES
('ID1', 'Process Issue'),
('ID1', 'Timing Issue'),
('ID2', 'Process Issue'),
('ID3', 'Process Issue'),
('ID4', 'Timing Issue'),
('ID4', 'Technical Issue');
-- DDL and sample data population, end

SELECT ParentRecID
	, DeployIssue = STRING_AGG(DeployIssue, ' | ')
FROM @tbl
GROUP BY ParentRecID;