// Loans by branch and genre // Pack: Library loans (Power Query Exercises, https://powerqueryexercises.com/downloads/library-loans) // // 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: // Loans the pack file loans.csv -> File.Contents(PackFolder & "loans.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 // 10 rows with columns Branch, Genre, Loans, OnLoan, AvgDaysOut. // 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\library-loans\", Loans = File.Contents(PackFolder & "loans.csv"), Source = Table.PromoteHeaders(Csv.Document(Loans, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(Source, {{"LoanDate", type date}, {"ReturnDate", type date}}, "en-US"), #"Days out" = Table.AddColumn(#"Changed Type", "DaysOut", each if [ReturnDate] = null then null else Duration.Days([ReturnDate] - [LoanDate]), Int64.Type), #"Grouped" = Table.Group(#"Days out", {"Branch", "Genre"}, {{"Loans", each Table.RowCount(_), Int64.Type}, {"OnLoan", each List.Count(List.Select([ReturnDate], (d) => d = null)), Int64.Type}, {"AvgDaysOut", each Number.Round(List.Average(List.RemoveNulls([DaysOut])) ?? 0, 1), type number}}), #"Sorted" = Table.Sort(#"Grouped", {{"Branch", Order.Ascending}, {"Genre", Order.Ascending}}) in #"Sorted"