Hello,
I'm new to Power Query but learning more everyday. I have a query that has 5 key values, BERV, MILT, VOLU, HOLI, WKHD. At anytime BERV, VOLU or MILT could appear or disappear as employees correct records.
I created 3 Test Employees in rows A2 - A5 for BERV, MILT, VOLU in the AbsTyp column to ensure their consistent presence in the accounting records.
After learning how to create a list, I was able to address many of my problems. However, a new issue arose when the Custom Column "VOLU OvHrs" was added for the column VOLU.
My list "AbsTypeList" changes depending on the source Column [AbsTyp]
The query functioned properly until an individual with VOLU modified their information, resulting in no remaining employees with VOLU. It is possible that another employee may have VOLU next month.
Could this query be rewritten to ensure it remains functional regardless of the presence of VOLU, BERV, or MILT in the records to prevent any disruptions in the process if these columns are altered or removed. By making the necessary adjustments, you can avoid the need to instruct the Off Shore team on modifying columns based on the existence of VOLU, BERV, or MILT.
I'm delegating this monthly task to our Philippines OffShore team and do not want to instruct them how manually fix the error "Expression.Error: The column 'VOLU' (or BERV or MILT) of the table wasn't found.
let
Source = Excel.CurrentWorkbook(){[Name="Master_Audit"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Workdate", type date}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([AbsTyp] <> null)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows2", "Work Location", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, true), {"Work Location.1", "Work Location.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Work Location.1", "Work Location"}}),
#"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns",{"Work Location.2"}),
#"Replaced Value7" = Table.ReplaceValue(#"Removed Columns6","Advanced Technologies & Life Sciences","ATLS",Replacer.ReplaceText,{"SBU"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value7", "Custom", each if [Work Schedule] = "Standard Work Schedule" then "Standard 8hr x 5 days" else if [Work Schedule] = "Standard 9x80 Schedule" then "Standard 9x80" else if [Work Schedule] = "10Hr X 4Days" then "10hr x 4 days" else null),
#"Reordered Columns5" = Table.ReorderColumns(#"Added Conditional Column",{"Pers.No.", "Employee Name", "AbsTyp", "CATS Hours", "Workdate", "WkEnd Date", "Approved by", "Remarks", "Status", "OUM", "CSD", "EEG", "EESG", "Pers.A", "Work Location", "SBE", "SBU", "EXPT", "Work Schedule", "Custom", "HR Region", "HR Ops"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns5",{{"Custom", "Std Work Schedule"}}),
#"Add Exclude Column" = Table.AddColumn(#"Renamed Columns1", "Excluding", each if Text.Contains([CSD], "Absence") then "Exclude" else if [EEG] <> "Salaried" then "Exclude" else if Text.Contains([Pers.A], "Caribbean") then "Exclude" else if Text.StartsWith([SBE], "Mission") then "Exclude" else if [HR Ops] = "Not in US" then "Exclude" else if Text.StartsWith([SBU], "TRS") then "Exclude" else if [SBU] = "Other HQ" then "Exclude" else " "),
#"Sorted Rows" = Table.Sort(#"Add Exclude Column",{{"HR Region", Order.Ascending}, {"Status", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Workdate","WkEnd Date","Approved by","Pers.A","CSD","EEG"}),
AbsTypeList = List.Buffer(List.Distinct(#"Removed Columns"[AbsTyp])),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", AbsTypeList, "AbsTyp", "CATS Hours", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,AbsTypeList),
#"Sorted Rows1" = Table.Sort(#"Replaced Value",{{"HR Region", Order.Ascending}, {"SBE", Order.Ascending}, {"SBU", Order.Ascending}, {"Work Location", Order.Ascending}, {"Employee Name", Order.Ascending}, {"Status", Order.Ascending}}),
#"BERV OvHrs" = Table.AddColumn(#"Sorted Rows1", "BERV Hrs", each if [Std Work Schedule] = "Standard 9x80" and [BERV] > 27 then "Per Work Schedule: BERV ⯅ 27 hrs by "&Number.ToText([BERV] - 27)&" hrs" else
if [Std Work Schedule] = "Standard 8hr x 5 days" and [BERV] > 24 then "Per Work Schedule: BERV ⯅ 24 hrs by "&Number.ToText([BERV] - 24)&" hrs" else
if [Std Work Schedule] = "10hr x 4 days" and [BERV] > 30 then "Per Work Schedule: BERV ⯅ 30 hrs by "&Number.ToText([BERV] - 30)&" hrs" else null),
#"VOLU OvHrs" = Table.AddColumn(#"BERV OvHrs", "VOLU Hrs", each if [VOLU] > 8 then "VOLU ⯅ "&Number.ToText([VOLU] - 8)&" hrs" else null),
#"MILT OvHrs" = Table.AddColumn(#"VOLU OvHrs", "MILT Hrs", each if [MILT] > 80 then "MILT ⯅ "&Number.ToText([MILT] - 80)&" hrs" else null),
#"HOLI OvHrs" = Table.AddColumn(#"MILT OvHrs", "HOLI Hrs", each if List.Sum({[HOLI] + [WKHD]}) > 56 then "HOLI ⯅ "&Number.ToText(List.Sum({[HOLI] + [WKHD]}) - 56)&" hrs" else null),
#"Added Abstyp Issues Column" = Table.AddColumn(#"HOLI OvHrs", "Abstyp Issues", each if [Status] = "Abstyp should NOT be HOLI" then "Abstyp should NOT be HOLI" else if [Status] = "Change Abstyp to HOLI" then "Change Abstyp to HOLI" else null),
#"Inserted Merged AbsType Col" = Table.AddColumn(#"Added Abstyp Issues Column", "Abstype Issues", each Text.Combine({[BERV Hrs], Text.From([VOLU Hrs], "en-US"), [MILT Hrs], [HOLI Hrs], [Abstyp Issues]}, " : "), type text),
#"Removed AbsType Columns" = Table.RemoveColumns(#"Inserted Merged AbsType Col",{"BERV Hrs", "VOLU Hrs", "MILT Hrs", "HOLI Hrs", "Abstyp Issues"}),
#"Replaced Value1" = Table.ReplaceValue(#"Removed AbsType Columns","","good",Replacer.ReplaceValue,{"Abstype Issues"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value1", each ([Excluding] = " ")),
#"Removed Columns4" = Table.RemoveColumns(#"Filtered Rows1",{"Excluding"}),
#"Sorted Rows2" = Table.Sort(#"Removed Columns4",{{"HR Region", Order.Ascending}, {"HR Ops", Order.Ascending}, {"SBE", Order.Ascending}, {"SBU", Order.Ascending}, {"Work Location", Order.Ascending}, {"Pers.No.", Order.Ascending}, {"Status", Order.Ascending}}),
#"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows2",{"Status", "Remarks", "Work Schedule"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns2", each ([Abstype Issues] <> "good")),
#"Reordered Columns2" = Table.ReorderColumns(#"Filtered Rows",{"Pers.No.", "Employee Name", "HR Region", "HR Ops", "SBE", "SBU", "Work Location", "Abstype Issues", "BERV", "VOLU", "MILT", "HOLI", "WKHD"})
in
#"Reordered Columns2"
I'm new to Power Query but learning more everyday. I have a query that has 5 key values, BERV, MILT, VOLU, HOLI, WKHD. At anytime BERV, VOLU or MILT could appear or disappear as employees correct records.
I created 3 Test Employees in rows A2 - A5 for BERV, MILT, VOLU in the AbsTyp column to ensure their consistent presence in the accounting records.
After learning how to create a list, I was able to address many of my problems. However, a new issue arose when the Custom Column "VOLU OvHrs" was added for the column VOLU.
My list "AbsTypeList" changes depending on the source Column [AbsTyp]
The query functioned properly until an individual with VOLU modified their information, resulting in no remaining employees with VOLU. It is possible that another employee may have VOLU next month.
Could this query be rewritten to ensure it remains functional regardless of the presence of VOLU, BERV, or MILT in the records to prevent any disruptions in the process if these columns are altered or removed. By making the necessary adjustments, you can avoid the need to instruct the Off Shore team on modifying columns based on the existence of VOLU, BERV, or MILT.
I'm delegating this monthly task to our Philippines OffShore team and do not want to instruct them how manually fix the error "Expression.Error: The column 'VOLU' (or BERV or MILT) of the table wasn't found.
let
Source = Excel.CurrentWorkbook(){[Name="Master_Audit"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Workdate", type date}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([AbsTyp] <> null)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows2", "Work Location", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, true), {"Work Location.1", "Work Location.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Work Location.1", "Work Location"}}),
#"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns",{"Work Location.2"}),
#"Replaced Value7" = Table.ReplaceValue(#"Removed Columns6","Advanced Technologies & Life Sciences","ATLS",Replacer.ReplaceText,{"SBU"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value7", "Custom", each if [Work Schedule] = "Standard Work Schedule" then "Standard 8hr x 5 days" else if [Work Schedule] = "Standard 9x80 Schedule" then "Standard 9x80" else if [Work Schedule] = "10Hr X 4Days" then "10hr x 4 days" else null),
#"Reordered Columns5" = Table.ReorderColumns(#"Added Conditional Column",{"Pers.No.", "Employee Name", "AbsTyp", "CATS Hours", "Workdate", "WkEnd Date", "Approved by", "Remarks", "Status", "OUM", "CSD", "EEG", "EESG", "Pers.A", "Work Location", "SBE", "SBU", "EXPT", "Work Schedule", "Custom", "HR Region", "HR Ops"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns5",{{"Custom", "Std Work Schedule"}}),
#"Add Exclude Column" = Table.AddColumn(#"Renamed Columns1", "Excluding", each if Text.Contains([CSD], "Absence") then "Exclude" else if [EEG] <> "Salaried" then "Exclude" else if Text.Contains([Pers.A], "Caribbean") then "Exclude" else if Text.StartsWith([SBE], "Mission") then "Exclude" else if [HR Ops] = "Not in US" then "Exclude" else if Text.StartsWith([SBU], "TRS") then "Exclude" else if [SBU] = "Other HQ" then "Exclude" else " "),
#"Sorted Rows" = Table.Sort(#"Add Exclude Column",{{"HR Region", Order.Ascending}, {"Status", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Workdate","WkEnd Date","Approved by","Pers.A","CSD","EEG"}),
AbsTypeList = List.Buffer(List.Distinct(#"Removed Columns"[AbsTyp])),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", AbsTypeList, "AbsTyp", "CATS Hours", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,AbsTypeList),
#"Sorted Rows1" = Table.Sort(#"Replaced Value",{{"HR Region", Order.Ascending}, {"SBE", Order.Ascending}, {"SBU", Order.Ascending}, {"Work Location", Order.Ascending}, {"Employee Name", Order.Ascending}, {"Status", Order.Ascending}}),
#"BERV OvHrs" = Table.AddColumn(#"Sorted Rows1", "BERV Hrs", each if [Std Work Schedule] = "Standard 9x80" and [BERV] > 27 then "Per Work Schedule: BERV ⯅ 27 hrs by "&Number.ToText([BERV] - 27)&" hrs" else
if [Std Work Schedule] = "Standard 8hr x 5 days" and [BERV] > 24 then "Per Work Schedule: BERV ⯅ 24 hrs by "&Number.ToText([BERV] - 24)&" hrs" else
if [Std Work Schedule] = "10hr x 4 days" and [BERV] > 30 then "Per Work Schedule: BERV ⯅ 30 hrs by "&Number.ToText([BERV] - 30)&" hrs" else null),
#"VOLU OvHrs" = Table.AddColumn(#"BERV OvHrs", "VOLU Hrs", each if [VOLU] > 8 then "VOLU ⯅ "&Number.ToText([VOLU] - 8)&" hrs" else null),
#"MILT OvHrs" = Table.AddColumn(#"VOLU OvHrs", "MILT Hrs", each if [MILT] > 80 then "MILT ⯅ "&Number.ToText([MILT] - 80)&" hrs" else null),
#"HOLI OvHrs" = Table.AddColumn(#"MILT OvHrs", "HOLI Hrs", each if List.Sum({[HOLI] + [WKHD]}) > 56 then "HOLI ⯅ "&Number.ToText(List.Sum({[HOLI] + [WKHD]}) - 56)&" hrs" else null),
#"Added Abstyp Issues Column" = Table.AddColumn(#"HOLI OvHrs", "Abstyp Issues", each if [Status] = "Abstyp should NOT be HOLI" then "Abstyp should NOT be HOLI" else if [Status] = "Change Abstyp to HOLI" then "Change Abstyp to HOLI" else null),
#"Inserted Merged AbsType Col" = Table.AddColumn(#"Added Abstyp Issues Column", "Abstype Issues", each Text.Combine({[BERV Hrs], Text.From([VOLU Hrs], "en-US"), [MILT Hrs], [HOLI Hrs], [Abstyp Issues]}, " : "), type text),
#"Removed AbsType Columns" = Table.RemoveColumns(#"Inserted Merged AbsType Col",{"BERV Hrs", "VOLU Hrs", "MILT Hrs", "HOLI Hrs", "Abstyp Issues"}),
#"Replaced Value1" = Table.ReplaceValue(#"Removed AbsType Columns","","good",Replacer.ReplaceValue,{"Abstype Issues"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value1", each ([Excluding] = " ")),
#"Removed Columns4" = Table.RemoveColumns(#"Filtered Rows1",{"Excluding"}),
#"Sorted Rows2" = Table.Sort(#"Removed Columns4",{{"HR Region", Order.Ascending}, {"HR Ops", Order.Ascending}, {"SBE", Order.Ascending}, {"SBU", Order.Ascending}, {"Work Location", Order.Ascending}, {"Pers.No.", Order.Ascending}, {"Status", Order.Ascending}}),
#"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows2",{"Status", "Remarks", "Work Schedule"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns2", each ([Abstype Issues] <> "good")),
#"Reordered Columns2" = Table.ReorderColumns(#"Filtered Rows",{"Pers.No.", "Employee Name", "HR Region", "HR Ops", "SBE", "SBU", "Work Location", "Abstype Issues", "BERV", "VOLU", "MILT", "HOLI", "WKHD"})
in
#"Reordered Columns2"