날짜 근무 시간 솔루션 사이의 LuckyTemplates

오늘의 블로그에서는 LuckyTemplates를 사용하여 날짜 간 총 작업 시간을 계산하는 Access Analytic의 Start Stop Challenge 에 대한 접근 방식을 시연합니다 . 이 블로그 하단에서 이 튜토리얼의 전체 비디오를 볼 수 있습니다 .

작업은 매월 및 날짜에 직원의 총 시간 수를 계산하는 것입니다. 몇 개월에 걸쳐 교차할 수 있는 시작 날짜와 종료 날짜를 제시하고 가능한 오타를 처리하는 상태를 고려했습니다. 

목차

• LuckyTemplates 날짜 간 근무 시간에 대한 데이터 세트
• 행 필터링
• 사용자 지정 열 추가
• LuckyTemplates 날짜 간 작업 시간: 변수
• LuckyTemplates 날짜 간 작업 시간: 예측
• LuckyTemplates 날짜 간 작업 시간: 집계 값
• 결론

LuckyTemplates 날짜 간 근무 시간에 대한 데이터 세트

파워 쿼리 편집기로 이동해 보겠습니다. 

이 챌린지에 대한 데이터는 다음과 같습니다. 직원 이름, 시작 날짜, 시작 시간, 중지 날짜, 중지 시간 및 상태에 대한 열이 있는 테이블이 있습니다.

행 필터링

상태에 따라 행을 선택하는 것부터 시작하겠습니다. 쿼리 편집기가 이 논리에 도움이 될 수 있습니다. 상태 옆에 있는 드롭다운 화살표를 클릭합니다 . 텍스트 필터를 선택 하고 다음으로 시작하지 않음을 선택합니다 . 

필터 행 창이 표시되면 "e"를 입력합니다. 그런 다음 확인을 클릭합니다 .

아직 아무 일도 일어나지 않을 것이며 대문자 "E"로 시작하는 데이터에 소문자 "e"와 "제외"를 쓰기 때문에 괜찮습니다.

이를 수정하기 위해 Text.StartsWith 의 선택적 세 번째 매개변수를 사용합니다 . 수식 입력줄에서 Text.StartsWith 함수의 끝으로 이동합니다 . 쉼표를 추가하고 Comparer.OrdinalIgnoreCase를 입력해 보겠습니다 . 확인 표시를 클릭하면 제외 행이 제거됩니다.

물론 이니셜 "E" 없이 "제외"를 잘못 입력한 사람이 있을 수 있습니다. 이 경우 상황에 따라 추가 논리를 만들 수 있습니다. 

함수를 확장하고 Text.Contains 를 사용하여 단어에 "X"가 포함되어 있지 않은지 테스트할 수 있습니다 . 그렇게 하려면 Text.Contains ([Status], “x”, Comparer.OrdinalIgnoreCase .

첫 번째 절과 비교하여 "e"를 "x"로 바꿨습니다. 대소문자를 무시하고 두 절을 모두 괄호로 묶습니다. 확인 표시를 클릭하면 Exclude 또는 Xclude 상태 를 포함하는 행이 없는 테이블이 표시됩니다 .

사용자 지정 열 추가

이제 이 챌린지의 주요 부분으로 가보겠습니다. UI를 사용하여 맞춤 열을 추가한 다음 편집기로 전환합니다. 

테이블의 왼쪽 상단 모서리에 있는 미니 테이블 아이콘 으로 시작합니다 . 그것을 클릭하고 드롭다운에서 Add Custom Column 을 선택합니다 . 

이 시점에서 우리는 나중에 필요할 데이터를 가져오는 데에만 관심이 있습니다. 그리고 여러 필드로 작업할 것이므로 대괄호로 표시된 레코드 이니셜라이저를 사용하여 레코드를 만들어 보겠습니다. 

변수를 만들고 사용 가능한 열을 할당해 보겠습니다. 시작 날짜와 동일한 SD를 입력하여 시작합니다 . 오른쪽 열에서 시작 날짜를 클릭하면 됩니다 .

변수 ST, ED 및 ET 에 대해 각각 Start Time, Stop Date 및 Stop Time 에 할당되도록 동일한 작업을 수행합니다 . 그런 다음 닫는 괄호로 레코드를 닫습니다. 확인을 클릭합니다 .

레코드가 있는 사용자 지정 열이 테이블에 추가됩니다. 녹화 옆의 공백을 클릭하면 미리보기 창이 열립니다. 이렇게 하면 이 행의 모든 ​​값을 가져올 것입니다.

LuckyTemplates 날짜 간 작업 시간: 변수

그런 다음 고급 편집기 창을 통해 변수의 형식을 지정할 수 있습니다 . 이것이 고급 편집기 창의 모습입니다. 

그런 다음 변수를 별도의 새 줄에 넣습니다.

이제 필요한 논리를 확장할 수 있습니다. 따라서 여기서 레코드 대신 중첩된 let 표현식을 사용할 수도 있습니다. 값이 할당된 여러 변수 또는 필드 이름이 있으므로 둘 중 하나가 작동합니다.

시작 날짜 는 현재 텍스트 값으로 작성되었으며 이를 적절한 날짜 형식으로 변환해야 합니다. 그렇게 하려면 Date.FromText 함수를 감싸십시오 . 

역시 문자열 형식인 시작 시간 의 경우 숫자로 변환해야 합니다. 그런 다음 이를 100으로 나누고 반올림하여 소수점 이하 자릿수를 제거할 수 있습니다. 

Number.From 함수를 사용하여 시작 날짜 값을 100 으로 나눕니다 . 그런 다음 다음 함수인 Number.Round 안에 래핑 하고 끝에  0을 추가하여 소수점 이하 자릿수 없이 반올림합니다.

종료 날짜 및 종료 시간 의 경우 각각 시작 날짜 및 시작 시간 에 있는 것과 동일한 함수를 복사 하고 적절한 변수로 바꿉니다.

변수의 형식이 올바르게 지정되면 몇 가지 추가 논리를 위해 레코드에 다른 필드를 만듭니다. Enter 키를 눌러 새 줄을 만듭니다 . 

첫 번째 날짜부터 종료 날짜까지의 날짜 목록을 구성해야 합니다. 우리는 그 날짜를 LD라고 부르고 List.Dates 함수를 사용할 것입니다 . 

이 함수의 첫 번째 매개변수는 SD 가 될 시작 날짜 입니다 . 그런 다음 숫자로 계산 하거나 시작 날짜 와 종료 날짜 의 차이를 원합니다 . Number.From 함수 다음에 ( ED – SD) + 1 을 ��용하여 이를 얻을 수 있습니다 . 다음 스크린샷 세트에는 "+1"이 표시되지 않지만 +1 이 있어야 합니다 .

세 번째 매개변수는 기간으로서의 단계 라고 하며 하루씩 증분하려고 합니다. Duration.From(1)을 사용하여 얻을 수 있습니다 . 

첫 번째 시작 날짜부터 중지 날짜까지의 날짜 목록을 만들었습니다. 다음에 우리가 원하는 것은 그것과 함께 가는 시간 목록을 만드는 것입니다. 

목록 시간에 대해 이 LT를 호출하겠습니다 . LT는 여러 날에 걸쳐 있는 경우와 약간 다른 논리가 필요한 하루의 기간을 가질 수 있습니다. 그래서 조건을 만들어야 합니다. 

"시작 날짜가 종료 날짜와 같으면 종료 시간에서 시작 시간을 뺀 시간"이라는 조건이 필요합니다. 결과는 목록 형식이어야 하므로 { } 로 표시되는 목록 이니셜라이저를 사용합니다 . 

이제 여러 날에 걸친 날짜 범위가 있는 경우 첫 번째 날짜는 24에서 시작 시간을 뺀 값이 됩니다. 우리는 이것을 else 문 으로 조건에 추가할 것입니다 . 다시 중괄호를 사용하여 목록으로 형식을 지정합니다.

이 시점에서 우리는 다른 전체 날짜를 고려하지 않았으므로 앰퍼샌드 기호(&)를 사용하여 여러 목록을 추가합니다. 하루 전체에 대해 하루 24시간을 나타내는 값 24로 목록을 만듭니다. 이를 위해  List.Repeat를 사용합니다 .

List.Repeat 함수를 사용하여 24를 포함하는 목록을 만들고 LD 의 일 수를 세어 여러 번 반복합니다 . 이를 달성하려면 List.Count(LD)를 사용한   다음 시작 날짜에 대한 별도의 목록이 있고 종료 시간에 대한 다른 목록을 생성하므로 2를 뺍니다. 

본질적으로 이렇게 하면 하루 종일 24시간만 포함된 목록이 생성됩니다. 

종료 시간에는 앰퍼샌드를 사용하여 목록으로 다시 추가할 수 있습니다. 그런 다음 목록 이니셜라이저를 사용하여 ET를 호출하겠습니다. 

지금까지 두 개의 큰 목록(날짜 목록과 시간 목록)이 있으며 길이는 서로 같습니다. 이 두 목록에서 단일 테이블을 구성할 수 있습니다. 

새 줄에서 t를 호출하고 Table.FromColumns 함수를 사용할 테이블에 대한 또 다른 변수를 만듭니다 . 이 함수는 목록을 목록으로 요구하며 우리는 LD를 사용할 것입니다. 

LD 에는 시작일부터 종료일까지의 개별 날짜가 포함되어 있습니다. LD를 전달하고 Date.EndOfMonth 함수를 호출 하여 해당 날짜 목록을 월말 날짜 값으로 변환할 수 있습니다 . 그런 다음 다음 줄에서 LT 도 얻습니다. 

Enter 키를 눌러 다른 줄을 만들고 테이블을 type table 로 설정합니다 . 테이블에는 두 개의 열이 있으며 첫 번째 열은 날짜 열입니다. 따라서 Date 열을 만들고 Date.Type 함수를 호출합니다 .

두 번째 열은 시간 열이며 정수입니다. 따라서 Hrs 열을 만들고 Int8.Type을 호출합니다 .

레코드를 닫는 닫는 괄호와 Table.AddColumn 함수 를 닫는 닫는 괄호가 있습니다 . 완료를 클릭하면 사용자 정의 열 아래에 레코드 목록이 표시됩니다. 

다시 각 레코드 옆의 공백을 클릭하면 화면 하단에 테이블이 열립니다. 

마지막 행에 t 테이블이 있습니다 . 레코드 중 하나를 자세히 살펴보겠습니다. 첫 번째 결과를 마우스 오른쪽 버튼으로 클릭하고 Add as New Query . 

이전과 동일한 요약이 표시되며 테이블을 클릭하면 날짜 및 시간 값을 가져와야 합니다 .

그러나 우리는 주로 최종 출력 테이블에 관심이 있습니다. 이에 집중하기 위해 Custom 을 마우스 오른쪽 버튼으로 클릭 하고 Delete 를 선택하여 도움말 쿼리를 제거해 보겠습니다 . 팝업 창에서 삭제를 클릭하여 확인합니다 .

삭제되면 초기 쿼리로 돌아가서 Record 옆의 공백을 다시 클릭할 수 있습니다. 이전과 동일한 결과가 표시됩니다. 하지만 이번에는 수식 막대를 확장하고 닫는 괄호 옆에 있는 [t]를 호출해 보겠습니다 .

Custom 열에서 각 Record가 Table 로 변경된 것을 볼 수 있습니다 . 

LuckyTemplates 날짜 간 작업 시간: 예측

우리가 보고 있는 테이블에는 관심 있는 열이 직원 이름과 사용자 지정 열 두 개뿐입니다. 프로젝션을 사용하여 이 테이블에서 두 열만 유지할 수 있습니다. 

수식 입력줄로 이동하여 닫는 괄호 옆에 유지하려는 필드를 대괄호 안에 넣어 선택합니다. 그런 다음 확인 표시를 누릅니다.

우리는 이 결과를 얻어야 합니다.

Custom  옆에 있는 화살표 아이콘을 클릭하여 중첩 테이블을 확장할 수 있습니다 . 원래 열 이름을 접두어로 사용을 선택 취소 하고 확인을 클릭하십시오.

테이블은 이렇게 나타나야 합니다.

LuckyTemplates 날짜 간 작업 시간: 집계 값

이제 남은 작업은 이러한 값을 집계하는 것입니다. 그렇게 하려면 직원 이름 열을 선택하고 변환으로 이동합니다. 

팝업 창에서 값 열 아래의 Hrs를 선택합니다 . 이것이 우리가 집계하려는 것이기 때문입니다. 그런 다음 Aggregate Value Function 아래에서 Sum을 선택합니다 . 그런 다음 확인을 클릭합니다.

최종 출력은 다음과 같습니다. 

LuckyTemplates에서 날짜 테이블 만들기 LuckyTemplates에서 비표준 날짜 테이블에 대해 가능한 가장 빠른 시간 비교 방법
DateTime 값: 초를 제거하는 방법

결론

이 블로그에서는 Access Analytic의 Start Stop Challenge에 대한 한 가지 접근 방식을 보았습니다. 각 직원이 한 달 동안 근무한 총 시간을 계산하는 방법과 데이터에서 발생할 수 있는 오타를 처리하는 방법을 배웠습니다. 

이 기술과 LuckyTemplates를 사용하면 날짜 간 근무 시간을 조직에서 쉽게 계산하거나 LuckyTemplates 지식과 기술을 심화하는 방법으로 계산할 수 있습니다.