Hi,
I am a novice Power Query user and I have broken my powerquery. Can you please help look over?
let
Source = Folder.Files("C:\Users\Accountant\\Spreadsheet Data\Pocketwatch\ShiftReport"),
#"Added Custom Column3" = Table.AddColumn(Source, "Week Ending", each let splitName = Splitter.SplitTextByDelimiter("Report", QuoteStyle.None)([Name]), splitsplitName1 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(splitName{1}?) in Text.Combine({Text.Middle([Name], 31, 2), "/", Text.Middle([Name], 29, 2), Text.Combine(List.Transform(splitsplitName1, each Text.Start(_, 4)), "/")}), type text),
#"Filtered Rows5" = Table.SelectRows(#"Added Custom Column3", let latest = List.Max(#"Added Custom Column3"[Date created]) in each [Date created] = latest),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows5", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Transform File" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}, {"Transform File.Column1", "Transform File.Column2", "Transform File.Column3", "Transform File.Column4", "Transform File.Column5", "Transform File.Column6", "Transform File.Column7", "Transform File.Column8", "Transform File.Column9", "Transform File.Column10", "Transform File.Column11", "Transform File.Column12", "Transform File.Column13", "Transform File.Column14", "Transform File.Column15"}),
#"Removed Top Rows" = Table.Skip(#"Expanded Transform File",4),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Rename"= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){0}, "Week Ending"}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(Rename, "Text Between Delimiters", each Text.BetweenDelimiters([Week Ending], ".", " ", {0, RelativePosition.FromEnd}, {0, RelativePosition.FromEnd}), type text),
#"Inserted Date" = Table.AddColumn(#"Inserted Text Between Delimiters", "Date", each Date.From([Text Between Delimiters]), type date),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Date",{"Week Ending", "Text Between Delimiters"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns",{{"Date", "Week Ending"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns4", "Roster", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Roster.1", "Roster.2", "Roster.3"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter",".","",Replacer.ReplaceText,{"Roster.2"}),
#"Filtered Rows2" = Table.SelectRows(#"Replaced Value", each ([Roster.1] = "BRS")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Roster.1", "LOC"}, {"Roster.2", "RosterName"}, {"Roster.3", "Program Type"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"RosterName", Order.Ascending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([RosterName] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Shift description", type text}, {"Assets", type text}, {"Tag/s", type text}, {"Client/s", type text}, {"Staff member/s", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Client/s", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Client/s.1", "Client/s.2", "Client/s.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"LOC", type text}, {"RosterName", type text}, {"Program Type", type text}, {"Client/s.1", type text}, {"Client/s.2", type text}, {"Client/s.3", type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "Client/s.1", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Client/s.1.1", "Client/s.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Client/s.1.1", type text}, {"Client/s.1.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Client/s.1.2"}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns1", "Client/s.2", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Client/s.2.1", "Client/s.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Client/s.2.1", type text}, {"Client/s.2.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"Client/s.2.2"}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Removed Columns2", "Client/s.3", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Client/s.3.1", "Client/s.3.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Client/s.3.1", type text}, {"Client/s.3.2", type text}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type4",{"Client/s.3.2"}),
#"Changed Type14" = Table.TransformColumnTypes(#"Removed Columns3",{{"Shift end date", type datetime}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type14", "Clients 1.1", each let splitClients11 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([#"Client/s.1.1"]) in Text.Combine({splitClients11{2}?, " ", Text.Proper(splitClients11{1}?)}), type text),
#"Added Custom Column1" = Table.AddColumn(#"Added Custom Column", "Clients 1.2", each let splitClients11 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([#"Client/s.2.1"]) in Text.Combine({splitClients11{2}?, " ", Text.Proper(splitClients11{1}?)}), type text),
#"Added Custom Column2" = Table.AddColumn(#"Added Custom Column1", "Clients 1.3", each let splitClients11 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([#"Client/s.3.1"]) in Text.Combine({splitClients11{2}?, " ", Text.Proper(splitClients11{1}?)}), type text),
#"Duplicated Column2" = Table.DuplicateColumn(#"Added Custom Column2", "Shift end date", "Shift end date - Date"),
#"Changed Type15" = Table.TransformColumnTypes(#"Duplicated Column2",{{"Shift end date - Date", type date}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type15", "Week Beginning", each Date.AddDays([Week Ending],-6)),
#"Changed Type16" = Table.TransformColumnTypes(#"Added Custom3",{{"Week Beginning", type date}}),
#"Added Conditional Column3" = Table.AddColumn(#"Changed Type16", "Prev Week", each if [#"Shift end date - Date"] < [Week Beginning] then "Prev" else "Current"),
#"Filtered Rows7" = Table.SelectRows(#"Added Conditional Column3", each ([Prev Week] = "Current")),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows7", "Client/s1", each if [Clients 1.1] = null then [RosterName] else [RosterName]),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Client/s1", type text}}),
#"Removed Columns4" = Table.RemoveColumns(#"Changed Type5",{"Client/s.1.1", "Client/s.2.1", "Client/s.3.1"}),
#"Removed Columns5" = Table.RemoveColumns(#"Removed Columns4",{"Clients 1.1"}),
#"Removed Columns6" = Table.RemoveColumns(#"Removed Columns5",{"Program Type", "Shift description", "Assets", "Tag/s"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns6", {"LOC", "RosterName", "Staff member/s", "Shift start date", "Shift end date", "Status", "Week Ending", "Checked in date", "Checked in staff member", "Checked out date", "Checked out staff member"}, "Attribute", "Value"),
#"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Client#Sch"}}),
#"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{{"Value", "Client"}}),
#"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Shift start date", type datetime}, {"Shift end date", type datetime}, {"Status", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type6", "Hours", each ([Shift end date]-[Shift start date])*24),
#"Changed Type7" = Table.TransformColumnTypes(#"Added Custom",{{"Hours", type number}}),
#"Added Overnight Shift" = Table.AddColumn(#"Changed Type7", "OvernightShift", each if DateTime.Date([Shift start date])=DateTime.Date([Shift end date]) then "Same Day" else "Overnight Shift"),
#"Remove Other Accom Rosters" = Table.SelectRows(#"Added Overnight Shift", each ([RosterName] <> "Accommodation Dobby" and [RosterName] <> "Accommodation Granger" and [RosterName] <> "Accommodation Potter" and [RosterName] <> "Accommodation Snape" and [RosterName] <> "Brisbane Office " and [RosterName] <> "Brisbane On-Call")),
#"Inserted Day Name" = Table.AddColumn(#"Remove Other Accom Rosters", "Day Name", each Date.DayOfWeekName([Shift start date]), type text),
#"Duplicated Column" = Table.DuplicateColumn(#"Inserted Day Name", "Shift start date", "Shift start date - Copy"),
#"Changed Type8" = Table.TransformColumnTypes(#"Duplicated Column",{{"Shift start date - Copy", type time}, {"Shift start date", type date}}),
#"Renamed Columns5" = Table.RenameColumns(#"Changed Type8",{{"Shift start date - Copy", "Shift start time"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns5", "Shift end date", "Shift end date - Copy"),
#"Changed Type9" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Shift end date - Copy", type time}}),
#"Renamed Columns6" = Table.RenameColumns(#"Changed Type9",{{"Shift end date - Copy", "Shift end time"}}),
#"Changed Type10" = Table.TransformColumnTypes(#"Renamed Columns6",{{"Shift end date", type date}}),
#"Renamed Columns9" = Table.RenameColumns(#"Changed Type10",{{"Hours", "HoursInclOvernight"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns9", {"Shift start date"}, #"QLD Public Holidays", {"Date"}, "QLD Public Holidays", JoinKind.LeftOuter),
#"Expanded QLD Public Holidays" = Table.ExpandTableColumn(#"Merged Queries", "QLD Public Holidays", {"Holiday"}, {"QLD Public Holidays.Holiday"}),
#"OvernightAdditionalHours" = Table.AddColumn(#"Expanded QLD Public Holidays", "OvernightAddHours", each if [OvernightShift] = "Same Day" then "None" else if [Shift start time] < SleepoverHourStart then "Additional Hours" else if [Shift end time] > SleepoverHourEnd then "Additional Hours" else "None"),
#"ONBeforeHours" = Table.AddColumn(#"OvernightAdditionalHours", "ONBeforeHours", each ((SleepoverHourStart-[Shift start time])*24),
#"Changed Type" = Table.AddColumn(#"ONBeforeHours","ONBeforeHours", each ([Shift end time]-SleepoverHourEnd)*24),
#"Changed Type17" = Table.TransformColumnTypes(#"ChangedType",{{"ONBeforeHours", type number}},{{"ONAfterHours", type number}}),
#"Added Conditional Column2" = Table.AddColumn(#"Changed Type17", "WeekdayCheck", each if [Day Name] = "Saturday" then "Weekend" else if [Day Name] = "Sunday" then "Weekend" else "Weekday"),
#"Changed Type11" = Table.TransformColumnTypes(#"Added Conditional Column2",{{"WeekdayCheck", type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type11", "Shift Type", each if [Day Name] = "Saturday" then "Saturday Shift" else if [Day Name] = "Sunday" then "Sunday Shift" else if [QLD Public Holidays.Holiday] <> null then "Public Holiday Shift" else if [Shift start time] >= #time(0, 1, 0) and [Shift start time] < #time(6, 0, 0) then "Night Shift" else if [Shift end time] >= #time(0, 1, 0) and [Shift end time] < #time(6, 0, 0) then "Night Shift" else if [Shift end time] >= #time(20, 0, 1) and [Shift end time] < #time(24, 0, 0) then "Afternoon Shift" else "Day Shift"),
#"Sorted Rows1" = Table.Sort(#"Added Conditional Column1",{{"Shift start time", Order.Ascending}}),
#"Filtered Rows3" = Table.SelectRows(#"Sorted Rows1", let latest = List.Max(#"Sorted Rows1"[Week Ending]) in each [Week Ending] = latest),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows3", List.Distinct(#"Filtered Rows3"[#"Shift Type"]), "Shift Type", "Hours", List.Sum),
#"Filtered Rows4" = Table.SelectRows(#"Pivoted Column", each ([#"Client#Sch"] = "Client/s1")),
#"Grouped Rows" = Table.Group(#"Filtered Rows4", {"Client"}, {{"Sunday", each List.Sum([Sunday Shift]), type nullable number}, {"Saturday", each List.Sum([Saturday Shift]), type nullable number}, {"Weekdays", each List.Sum([Day Shift]), type nullable number}, {"Afternoon", each List.Sum([Afternoon Shift]), type nullable number}, {"P/H", each List.Sum([Public Holiday Shift]), type nullable number}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Grouped Rows", {{"P/H", null}}),
#"Merged Queries1" = Table.NestedJoin(#"Replaced Errors", {"Client"}, #"Payroll Report KMS", {"Client"}, "Payroll Report KMS", JoinKind.LeftOuter),
#"Expanded Payroll Report KMS" = Table.ExpandTableColumn(#"Merged Queries1", "Payroll Report KMS", {"Kms"}, {"Kms"}),
#"Renamed Columns7" = Table.RenameColumns(#"Expanded Payroll Report KMS",{{"Kms", "KMS"}}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns7", {"Client"}, "Attribute", "Value"),
#"Changed Type13" = Table.TransformColumnTypes(#"Unpivoted Columns1",{{"Client", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type13",{{"Client", Text.Trim, type text}}),
#"Merged Queries2" = Table.NestedJoin(#"Trimmed Text", {"Client"}, NamesFunding, {"Participant"}, "NamesFunding", JoinKind.LeftOuter),
#"Expanded NamesFunding" = Table.ExpandTableColumn(#"Merged Queries2", "NamesFunding", {"NDIS#", "Email", "Type", "Funding Type", "Funding Level"}, {"NamesFunding.NDIS#", "NamesFunding.Email", "NamesFunding.Type", "NamesFunding.Funding Type", "NamesFunding.Funding Level"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded NamesFunding", "Merged", each Text.Combine({[NamesFunding.Funding Type], [NamesFunding.Funding Level], [Attribute]}, ""), type text),
#"Renamed Columns8" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "Key"}}),
#"Merged Queries3" = Table.NestedJoin(#"Renamed Columns8", {"Key"}, Rates, {"Key"}, "Rates", JoinKind.LeftOuter),
#"Expanded Rates" = Table.ExpandTableColumn(#"Merged Queries3", "Rates", {"Code", "QLD Rate", "Desccription"}, {"Rates.Code", "Rates.QLD Rate", "Rates.Desccription"}),
#"Filtered Rows6" = Table.SelectRows(#"Expanded Rates", each true),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows6", "Total", each [Rates.QLD Rate]*[Value]),
#"Changed Type12" = Table.TransformColumnTypes(#"Added Custom2",{{"Total", Currency.Type}}),
#"Sorted Rows2" = Table.Sort(#"Changed Type12",{{"Client", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows2", "Index", 1, 1, Int64.Type),
#"Replaced Value1" = Table.ReplaceValue(#"Added Index",".","",Replacer.ReplaceText,{"NamesFunding.NDIS#"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",":","",Replacer.ReplaceText,{"NamesFunding.NDIS#"})
in
#"Replaced Value2"
