Forum Discussion
dblencowe
Apr 18, 2020Copper Contributor
Looking to combine csv data into a table
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 Quest...
SergeiBaklan
Apr 18, 2020Diamond Contributor
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