Token Comma Expected

Anonymous — Fri, 20 May 2022 06:05:50 GMT

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"

Re: Token Comma Expected

Vijay_A_Verma — Fri, 20 May 2022 06:19:58 GMT

At which step do you get this error?

Re: Token Comma Expected

Anonymous — Fri, 20 May 2022 06:34:44 GMT

I think it was somewhere around here

#"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}}),

Re: Token Comma Expected

Vijay_A_Verma — Fri, 20 May 2022 07:12:31 GMT

This line was problematic

#"ONBeforeHours" = Table.AddColumn(#"OvernightAdditionalHours", "ONBeforeHours", each ((SleepoverHourStart-[Shift start time])*24),

where you had one additional (. Making it

(SleepoverHourStart will solve you issue.

Also note - Next line is a Changed Type and you have already used it once. So, you will need to change it some other name say 18. Hence Change Type17 will also need change.

Hence, replace those 3 lines with this

#"ONBeforeHours" = Table.AddColumn(#"OvernightAdditionalHours", "ONBeforeHours", each (SleepoverHourStart-[Shift start time])*24), #"Changed Type18" = Table.AddColumn(#"ONBeforeHours","ONBeforeHours", each ([Shift end time]-SleepoverHourEnd)*24), #"Changed Type17" = Table.TransformColumnTypes(#"ChangedType18",{{"ONBeforeHours", type number}},{{"ONAfterHours", type number}}),

Re: Token Comma Expected

Anonymous — Fri, 20 May 2022 07:47:51 GMT

Thanks so much it worked!

topic Re: Token Comma Expected in Power Query

Token Comma Expected

Re: Token Comma Expected

Re: Token Comma Expected

Re: Token Comma Expected

Re: Token Comma Expected