Looking to combine csv data into a table

Question

I am running Excel 365 (1908), have a csv from a dB and need to massage data. Comes in with up to 8 rows of data per user. Need to convert to table and place data in correct column depending on Question
User | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8
1000 | A1 | A2 | A3 | A4 | A5 | A6 | A7 | A8
=====<sample csv>====Actual data 3000 rows
"User","Question","Answer"

1000,Q1,A1
1000,Q8,A8
1000,Q3,A3
1001,Q4,A4
1001,Q3,A1
1002,Q2,A2
1002,Q3,A3

Figured would need to sort data by User and then Question. If I can build a macro to do this that would be helpful since the data will need to be updated weekly.

This is to be able to generate a report for our team of volunteers.

Thanks, Deb

sergeibaklan · Answer

dblencowe&nbsp;
That could be done by Power Query - query csv file or directly database, pivot on questions and return table back with some cosmetic

Script is like
let
    Source = Csv.Document(File.Contents("C:\Test\users.csv"),
        [Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    PromotHeaders = Table.PromoteHeaders(
        Source, [PromoteAllScalars=true]
    ),
    SortByQuestion = Table.Sort(
        PromotHeaders,
        {{"Question", Order.Ascending}}
    ),
    PivotOnQuestion = Table.Pivot(
        SortByQuestion,
        List.Distinct(SortByQuestion[Question]),
        "Question", "Answer"
    ),
    SortByUser = Table.Sort(
        PivotOnQuestion,
        {{"User", Order.Ascending}}
    )
in
    SortByUser

Forum Discussion

Looking to combine csv data into a table

1 Reply