Appending Column of Duplicate Data into One Row

[STIBBS_807]

Need more assistance from the community. 

I have a excel spreadsheet with Monitor data in it.  It looks like this:

I would like to transform the data into this:

Here is my code so far:

let
Source = Csv.Document(File.Contents(" ....\Testing\Displays.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Computer", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Manufacturer", "Model", "Description"})
in
#"Removed Columns"

I have tried other Merge Rows into One Row solutions from this community but with no success.  Wonder if someone would assist me in flattening out my data.

Thank you in advance.😊

[HotChilli · Accepted solution]

We always prefer a data sample rather than a picture.

Anyway, the strategy is to add an Index (per Computer category) then perform a pivot on this new Index column to create the new column headings.

Step1 Group by Computer with new column "all" (choose 'All Rows')

Step 2 Add New Column with code like:

Table.AddIndexColumn([all], "Count", 1, 1)

Step 3 Expand the New Column (return everything and we'll tidy up columns after)

Step 4 Remove redundant columns.

Step 5 Select "Count" column and Pivot (from the Transform menu). Choose the Values column and 'Don't aggregate'

That should be it.

-------------------------

Let me know how it goes as that's some reasonably advanced operations. 

[Ashish_Mathur]

Hi,

Share the download link of the Excel file and in another tab of the file, show the expected result clearly.

[HotChilli · Accepted solution]

We always prefer a data sample rather than a picture.

Anyway, the strategy is to add an Index (per Computer category) then perform a pivot on this new Index column to create the new column headings.

Step1 Group by Computer with new column "all" (choose 'All Rows')

Step 2 Add New Column with code like:

Table.AddIndexColumn([all], "Count", 1, 1)

Step 3 Expand the New Column (return everything and we'll tidy up columns after)

Step 4 Remove redundant columns.

Step 5 Select "Count" column and Pivot (from the Transform menu). Choose the Values column and 'Don't aggregate'

That should be it.

-------------------------

Let me know how it goes as that's some reasonably advanced operations. 

[STIBBS_807 · Accepted solution]

Here is a sample of my data

ComputerManufacturerModelDescriptionSerial Number

8WOODSDRIVE	ACR	ACER V243H	Generic PnP Monitor	LHG080334231
8WOODSDRIVE	ACR	ACER X233H	Generic PnP Monitor	LG1080204220
FFD001	ACR	V277U	Generic PnP Monitor	TDCAA002852A
FFD001	GSM	24MB35	Generic PnP Monitor	610NTMX00419
FFD001	GSM	24MB35	Generic PnP Monitor	610NTTQ00417
FFD601	(Standard monitor types)		Generic Non-PnP Monitor
FFD602	ACR	Acer S231HL	Generic PnP Monitor	LNZ080024214
FFD603	ACR	Acer S231HL	Generic PnP Monitor	LNZ080024214
FFD604	ACR	Acer S231HL	Generic PnP Monitor	LNZ080024214
FFD605	(Standard monitor types)		Generic Non-PnP Monitor
FFD606	ACR	Acer H236HL	Generic PnP Monitor	LX1AA0034210
FFD607	ACR	Acer S231HL	Generic PnP Monitor	LNZ080024214
FFD608	GSM	24MB35	Generic PnP Monitor	609NTABCX552
FFD608	GSM	24MB35	Generic PnP Monitor	610NTSU00414
FFD609	ACR	Acer S231HL	Generic PnP Monitor	LNZ080024214
FFD610	ACR	Acer S231HL	Generic PnP Monitor	LNZ080024214
FFD612	(Standard monitor types)		Generic Non-PnP Monitor
FFD613	ACR	Acer B236HL	Generic PnP Monitor	LYXAA0014200
FFD613	ACR	V233H	Generic PnP Monitor	LGT0C0444060
FFD614	ACR	ACER V243H	Generic PnP Monitor	LHG080334231
FFD614	ACR	V233H	Generic PnP Monitor	LGT0C0444060
FFD615	ACR	ACER X233H	Generic PnP Monitor	LG1080204220
FFD616	ACR	S240HL	Generic PnP Monitor	LU50D0128532