Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am getting the issue 'token comma expected' with this query I wrote up in advanced query editor. Selecting 'show error' highlights "in" on the second last row - can anyone advise how to fix this?
let
Source = Folder.Files("P:\Workforce Planning 2018\Dashboards\Datsets\_A. Data Exports\D2_LEAVE BAL"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from D2_LEAVE BAL", each #"Transform File from D2_LEAVE BAL"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from D2_LEAVE BAL"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from D2_LEAVE BAL", Table.ColumnNames(#"Transform File from D2_LEAVE BAL"(#"Sample File (5)"))),
#"Added Custom1" = Table.AddColumn(#"Added Custom1", "FTE", each (([#"Hours/Week"]/38)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "ExcessThresholdHrs", each ([#"Hours/Week"])*4),
#"Added Custom4" = Table.AddColumn(#"Added Custom2", "Lvebal.FTE.hrs", each ([Total Leave Hrs]/7.6)/([#"FTE"]/100)),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "ExcessAnnLve.FTE.40days", each ([Lvebal.FTE.hrs]-40)*([#"FTE"]/100)),
#"Added Conditional Column2" = Table.AddColumn(#"Added Custom5", "ExcessAnnLve.Group", each if [ExcessAnnLve.FTE.40days] <= 0 then 0 else if [ExcessAnnLve.FTE.40days] <= 5 then "5< days" else if [ExcessAnnLve.FTE.40days] <= 15 then "6-15 day" else if [ExcessAnnLve.FTE.40days] <= 25 then "16-25 days" else if [ExcessAnnLve.FTE.40days] <= 50 then "26-50 days" else "50=> days"),
#"Added Conditional Column3" = Table.AddColumn(#"Replaced Value9", "Female Count", each if [Gender] = "Female" then 1 else 0),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column3", "Male Count", each if [Gender] = "Male" then 1 else 0)
in
#"Added Conditional Column5"
Solved! Go to Solution.
Hi @Anonymous,
Here I suggest to update your code as below.
let Source = Folder.Files("P:\Workforce Planning 2018\Dashboards\Datsets\_A. Data Exports\D2_LEAVE BAL"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from D2_LEAVE BAL", each #"Transform File from D2_LEAVE BAL"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from D2_LEAVE BAL"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from D2_LEAVE BAL", Table.ColumnNames(#"Transform File from D2_LEAVE BAL"(#"Sample File (5)"))), #"Added Custom1" = Table.AddColumn(#"Expanded Table Column1", "FTE", each (([#"Hours/Week"]/38)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ExcessThresholdHrs", each ([#"Hours/Week"])*4), #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Lvebal.FTE.hrs", each ([Total Leave Hrs]/7.6)/([#"FTE"]/100)), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "ExcessAnnLve.FTE.40days", each ([Lvebal.FTE.hrs]-40)*([#"FTE"]/100)), #"Added Conditional Column2" = Table.AddColumn(#"Added Custom5", "ExcessAnnLve.Group", each if [ExcessAnnLve.FTE.40days] <= 0 then 0 else if [ExcessAnnLve.FTE.40days] <= 5 then "5< days" else if [ExcessAnnLve.FTE.40days] <= 15 then "6-15 day" else if [ExcessAnnLve.FTE.40days] <= 25 then "16-25 days" else if [ExcessAnnLve.FTE.40days] <= 50 then "26-50 days" else "50=> days"), #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Female Count", each if [Gender] = "Female" then 1 else 0), #"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column3", "Male Count", each if [Gender] = "Male" then 1 else 0) in #"Added Conditional Column5"
Regards,
Frank
Hi @Anonymous,
Here I suggest to update your code as below.
let Source = Folder.Files("P:\Workforce Planning 2018\Dashboards\Datsets\_A. Data Exports\D2_LEAVE BAL"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from D2_LEAVE BAL", each #"Transform File from D2_LEAVE BAL"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from D2_LEAVE BAL"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from D2_LEAVE BAL", Table.ColumnNames(#"Transform File from D2_LEAVE BAL"(#"Sample File (5)"))), #"Added Custom1" = Table.AddColumn(#"Expanded Table Column1", "FTE", each (([#"Hours/Week"]/38)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ExcessThresholdHrs", each ([#"Hours/Week"])*4), #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Lvebal.FTE.hrs", each ([Total Leave Hrs]/7.6)/([#"FTE"]/100)), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "ExcessAnnLve.FTE.40days", each ([Lvebal.FTE.hrs]-40)*([#"FTE"]/100)), #"Added Conditional Column2" = Table.AddColumn(#"Added Custom5", "ExcessAnnLve.Group", each if [ExcessAnnLve.FTE.40days] <= 0 then 0 else if [ExcessAnnLve.FTE.40days] <= 5 then "5< days" else if [ExcessAnnLve.FTE.40days] <= 15 then "6-15 day" else if [ExcessAnnLve.FTE.40days] <= 25 then "16-25 days" else if [ExcessAnnLve.FTE.40days] <= 50 then "26-50 days" else "50=> days"), #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Female Count", each if [Gender] = "Female" then 1 else 0), #"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column3", "Male Count", each if [Gender] = "Male" then 1 else 0) in #"Added Conditional Column5"
Regards,
Frank
Hi @Anonymous,
Does that make sense? If so , kindly mark the answer as a solution to close the case, thanks in advance.
Regards,
Frank
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |