Table consolidation help

[patchkek]

Hello,

I have a table that looks like this

Customer Number	Date Visited	Rating
1	5/21/2018	1
2	2/7/2017	4
3	4/2/2018	2
2	5/6/2017	5
4	9/8/2017	4
5	11/25/2017	2
4	3/2/2017	5
6	6/8/2018	3
7	7/22/2018	2
7	2/16/2018	1
4	2/1/2018	4
3	12/14/2017	5
2	12/1/2017	1
1	8/8/2017	2
2	9/6/2017	3
3	5/6/2017	2
5	2/16/2018	4
4	2/7/2017	5
2	7/22/2018	2
5	8/19/2018	3
6	1/16/2018	4
1	10/17/2017	4
2	7/5/2017	4
3	6/6/2017	2

<colgroup><col><col><col></colgroup><tbody>
</tbody>

And I would like to transpose / consolidate / combine to look like this:

Customer Number	Date Visited	Rating	Date Visited2	Rating3	Date Visited4	Rating5	Date Visited6	Rating7	Date Visited8	Rating9
2	2/7/2017	4	12/1/2017	1	9/6/2017	3	7/22/2018	2	7/5/2017	4
3	4/2/2018	2	12/14/2017	5	5/6/2017	2	6/6/2017	2
4	9/8/2017	4	3/2/2017	5	2/1/2018	4	2/7/2017	5

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Any help/advise you can offer?

Thanks so much!

 

[sandy666]

at first glance you can try with PowerQuery

Customer Number

Date Visited.1

Rating.1

Date Visited.2

Rating.2

Date Visited.3

Rating.3

Date Visited.4

Rating.4

Date Visited.5

Rating.5

Date Visited.6

Rating.6

1​

08/08/2017​

2​

17/10/2017​

4​

21/05/2018​

1​

2​

07/02/2017​

4​

06/05/2017​

5​

05/07/2017​

4​

06/09/2017​

3​

01/12/2017​

1​

22/07/2018​

2​

3​

06/05/2017​

2​

06/06/2017​

2​

14/12/2017​

5​

02/04/2018​

2​

4​

07/02/2017​

5​

02/03/2017​

5​

08/09/2017​

4​

01/02/2018​

4​

5​

25/11/2017​

2​

16/02/2018​

4​

19/08/2018​

3​

6​

16/01/2018​

4​

08/06/2018​

3​

7​

16/02/2018​

1​

22/07/2018​

2​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Number", Int64.Type}, {"Date Visited", type date}, {"Rating", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer Number", Order.Ascending}, {"Date Visited", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer Number"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date Visited", each Table.Column([Count],"Date Visited")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Date Visited", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Rating", each Table.Column([Count],"Rating")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Rating", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values1", "Date Visited", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Date Visited.1", "Date Visited.2", "Date Visited.3", "Date Visited.4", "Date Visited.5", "Date Visited.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date Visited.1", type date}, {"Date Visited.2", type date}, {"Date Visited.3", type date}, {"Date Visited.4", type date}, {"Date Visited.5", type date}, {"Date Visited.6", type date}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Rating", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rating.1", "Rating.2", "Rating.3", "Rating.4", "Rating.5", "Rating.6"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Rating.1", Int64.Type}, {"Rating.2", Int64.Type}, {"Rating.3", Int64.Type}, {"Rating.4", Int64.Type}, {"Rating.5", Int64.Type}, {"Rating.6", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Customer Number", "Count", "Date Visited.1", "Rating.1", "Date Visited.2", "Rating.2", "Date Visited.3", "Rating.3", "Date Visited.4", "Rating.4", "Date Visited.5", "Rating.5", "Date Visited.6", "Rating.6"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Count"})
in
    #"Removed Columns"[/SIZE]