Consolidating multiple rows into one row with row data from some columns across page

[Edlin59]

Hello. I have multiple rows of data that I would like to consolidate into one row based on information in a number of columns. In the example 1 below I would like to consolidate all rows based on UniqueId, however would like the rows for Product & Question columns combined across the page with Answer displayed as a row under the combined columns – as per example 2.

Example 1

[TABLE="width: 849"]
<colgroup><col><col><col><col><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD]UniqueId[/TD]
[TD]Name[/TD]
[TD]Col3[/TD]
[TD]Col4[/TD]
[TD]Col5[/TD]
[TD]Col6[/TD]
[TD]Product[/TD]
[TD]Question[/TD]
[TD]Answer[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q4[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q5[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q6[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q7[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q8[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q9[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q10[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q11[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q12[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q1[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q4[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q5[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q6[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q7[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q8[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q9[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q10[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q11[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]ProdA[/TD]
[TD]Q12[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q1[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q4[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q5[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q6[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q7[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q8[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q9[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q10[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q11 [/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]ProdB[/TD]
[TD]Q12[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]

Example 2

[TABLE="width: 1418"]
<colgroup><col><col><col><col><col span="3"><col span="4"><col span="5"><col span="2"></colgroup><tbody>[TR]
[TD]UniqueId[/TD]
[TD]Name[/TD]
[TD]Col3[/TD]
[TD]Col4[/TD]
[TD]Col5[/TD]
[TD]Col6[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[TD]ProdA[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Red[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]1/12/2018[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[TD]Q6[/TD]
[TD]Q7[/TD]
[TD]Q8[/TD]
[TD]Q9[/TD]
[TD]Q10[/TD]
[TD]Q11[/TD]
[TD]Q12[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Blue[/TD]
[TD]DC[/TD]
[TD]White[/TD]
[TD]High[/TD]
[TD]12/12/2018[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]NA[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Green[/TD]
[TD]Auto[/TD]
[TD]White[/TD]
[TD]Low[/TD]
[TD]4/12/2018[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Help appreciated

 

[Edlin59]

Hello there. Thank you very much for this. Based on the M-code above I have adapted to include more columns however, I receive the following;

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=Table
Type=Type

The code is as follows;

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", type text}, {" Name", type text}, {" Other Name", type text}, {"Reason", type text}, {"Int Date", type date}, {"Access", type text}, {"Interaction Type", type text}, {"Disc ?", type text}, {"Action required", type text}, {"Est Date", type date}, {"Completed", type date}, {"Contact", type text}, {"New Question ", type text}, {"Product", type text}, {"Answer", type text}}), #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"New Question ", "Product"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns", {"Merged"}), "Merged", "Answer") in #"Pivoted Column"

 

[sandy666]

first TRIM spaces from headers
next try this:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueId", type text}, {"Name", type text}, {"Other Name", type text}, {"Reason", type text}, {"Int Date", type date}, {"Access", type text}, {"Interaction Type", type text}, {"Disc ?", type text}, {"Action required", type text}, {"Est Date", type date}, {"Completed", type date}, {"Contact", type text}, {"New Question", type text}, {"Product", type text}, {"Answer", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"New Question", "Product"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Answer")

in
    #"Pivoted Column"[/SIZE]

your problem is here
your code: #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns", {"Merged"}), "Merged", "Answer")
my code: #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Answer")

if you post any code use [CODE]your code here[/CODE]

 

[Edlin59]

Thank you for your help with this. Worked perfectly

 

[sandy666]

you are welcome