// Budget as a long table // Pack: Department budget (wide months) (Power Query Exercises, https://powerqueryexercises.com/downloads/department-budget-wide) // // 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: // BudgetWorkbook the pack file budget.xlsx -> File.Contents(PackFolder & "budget.xlsx") // 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 // 30 rows with columns Department, CostCentre, Month, Amount, MonthNo. // 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\department-budget-wide\", BudgetWorkbook = File.Contents(PackFolder & "budget.xlsx"), Source = Excel.Workbook(BudgetWorkbook, true), #"Budget" = Source{[Item = "Budget", Kind = "Sheet"]}[Data], #"Unpivoted" = Table.UnpivotOtherColumns(#"Budget", {"Department", "CostCentre"}, "Month", "Amount"), #"Month number" = Table.AddColumn(#"Unpivoted", "MonthNo", each List.PositionOf({"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, [Month]) + 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Month number", {{"Amount", type number}}) in #"Changed Type"