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

UniqueId	Name	Col3	Col4	Col5	Col6	Product	Question	Answer
Name1	Red	DC	White	High	1/12/2018	ProdA	Q1	Yes
Name1	Red	DC	White	High	1/12/2018	ProdA	Q2	Yes
Name1	Red	DC	White	High	1/12/2018	ProdA	Q3	Yes
Name1	Red	DC	White	High	1/12/2018	ProdA	Q4	No
Name1	Red	DC	White	High	1/12/2018	ProdA	Q5	No
Name1	Red	DC	White	High	1/12/2018	ProdA	Q6	No
Name1	Red	DC	White	High	1/12/2018	ProdA	Q7	NA
Name1	Red	DC	White	High	1/12/2018	ProdA	Q8	No
Name1	Red	DC	White	High	1/12/2018	ProdA	Q9	Yes
Name1	Red	DC	White	High	1/12/2018	ProdA	Q10	No
Name1	Red	DC	White	High	1/12/2018	ProdA	Q11	No
Name1	Red	DC	White	High	12/12/2018	ProdA	Q12	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q1	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q2	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q3	Yes
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q4	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q5	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q6	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q7	Yes
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q8	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q9	Yes
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q10	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q11	No
Name2	Blue	DC	White	High	12/12/2018	ProdA	Q12	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q1	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q2	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q3	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q4	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q5	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q6	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q7	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q8	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q9	Yes
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q10	Yes
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q11	No
Name3	Green	Auto	White	Low	4/12/2018	ProdB	Q12	NA

<colgroup><col><col><col><col><col span="3"><col span="2"></colgroup><tbody>
</tbody>

Example 2

UniqueId

Name

Col3

Col4

Col5

Col6

ProdA

ProdA

ProdA

ProdA

ProdA

ProdA

ProdA

ProdA

ProdA

ProdA

ProdA

ProdA

Name1

Red

DC

White

High

1/12/2018

Q1

Q2

Q3

Q4

Q5

Q6

Q7

Q8

Q9

Q10

Q11

Q12

Name2

Blue

DC

White

High

12/12/2018

Yes

Yes

Yes

No

No

No

NA

No

Yes

No

No

No

Name3

Green

Auto

White

Low

4/12/2018

No

No

Yes

No

No

No

Yes

No

Yes

No

No

No

<colgroup><col><col><col><col><col span="3"><col span="4"><col span="5"><col span="2"></colgroup><tbody>
</tbody>

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