// Average score per question // Pack: Survey responses (Power Query Exercises, https://powerqueryexercises.com/downloads/survey-responses-pivot) // // Source bindings // In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or // Power BI Desktop the first steps of this query do that binding instead: // Responses the pack file responses.csv -> File.Contents(PackFolder & "responses.csv") // Set PackFolder to the folder where you extracted the pack (keep the trailing backslash). // // Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns // 4 rows with columns Question, Answers, Average. // It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook. // Independent practice material; not affiliated with or endorsed by Microsoft. let PackFolder = "C:\PQE\survey-responses-pivot\", Responses = File.Contents(PackFolder & "responses.csv"), Source = Table.TransformColumnTypes(Table.PromoteHeaders(Csv.Document(Responses, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])), {{"Score", Int64.Type}}), #"Grouped" = Table.Group(Source, {"Question"}, {{"Answers", each Table.RowCount(_), Int64.Type}, {"Average", each Number.Round(List.Average([Score]), 2), type number}}), #"Sorted" = Table.Sort(#"Grouped", {{"Question", Order.Ascending}}) in #"Sorted"