영원한 휴일 달력 – POTW #12 (파워 쿼리 솔루션)

에 대한 제 솔루션을 살펴보겠습니다 . 이 챌린지를 위해 LuckyTemplates의 금주의 문제 챌린지를 다시 방문하여 특정 날짜와 상대적인 날짜를 혼합하여 영구 휴일 달력을 만들었습니다. 그러나 이번에는 파워 쿼리 만 사용합니다 . 이 블로그 하단에서 이 튜토리얼의 전체 비디오를 볼 수 있습니다.

문제 분석 자체에 갇혀 있는 경우 다시 방문 하여 이 문제를 해결하는 데 도움이 될 수 있는 요소가 접근 방식에 있는지 확인할 수 있습니다.

제출물을 읽는 동안 사용자 인터페이스를 활용 하고 사용자 지정 기능을 작성하여 영구 달력을 만드는 사람들을 보았습니다 . 각 항목을 분석하는 것이 즐거웠고 영감을 얻고 싶다면 똑같이 하시길 권장합니다.

이 시나리오를 통해 직접 작업할 때 여러 솔루션을 만들게 되었습니다. 내가 함께 갔던 최고의 사례는 문제를 더 작은 조각으로 분해 할 수 있을 뿐만 아니라 가시화할 수 있는 방법을 보여줍니다 . 이는 오류나 예기치 않은 결과가 발생할 경우 매우 유용합니다.

목차

• 퍼페추얼 캘린더 제작의 출발점
• 날짜 테이블에서 연도 검색
• 일 열에서 숫자 얻기
• 영구 휴일 달력에 대한 사용자 지정 함수 호출
• 날짜 목록 만들기
• 조건부 논리 적용
• 영구 휴일 달력에 대한 M 코드 검토
• 결론

퍼페추얼 캘린더 제작의 출발점

이 원시 공휴일 테이블은 우리의 출발점입니다. 날짜를 맞추려면 특정 날짜에 대한 연도, 월, 일이 필요합니다. 빠진 것은 연도뿐입니다.

그러나 상대 날짜의 경우 조금 더 많은 작업을 수행해야 합니다. 먼저 해당 요일 열을 숫자와 요일로 분할해야 합니다. 해당 단계를 검토해 보겠습니다.

날짜 테이블에서 연도 검색

이 솔루션을 동적으로 만들기 위해 날짜 테이블 에서 연도를 검색하겠습니다 . 수식 입력줄에서 프로젝션을 사용하여 날짜 테이블에서 단일 열 테이블을 가져온 것을 볼 수 있습니다. 물론 이것은 중복을 포함합니다. 이를 제거하기 위해 Table.Distinct 를 추가했습니다 .

이 테이블 옆의 공백 옆을 클릭하면 내 날짜 테이블 쿼리에 고유한 연도가 포함된 목록도 표시됩니다.

우리는 월 이름을 받았지만 실제로는 월 번호가 필요합니다 . 이제 연도를 월 이름에 연결할 때 Date.From 함수를 사용하여 해당 월의 1일을 날짜 값으로 반환할 수 있습니다.

Date.Month 를 추가하면 해당 특정 날짜의 월 번호만 남게 됩니다. 1월의 경우 1을 반환하고 2월의 경우 2를 반환하는 식입니다.

일 열에서 숫자 얻기

일 열에서 숫자를 가져오기 위해 열을 분할하지 않고 대신 값을 추출하기로 결정했습니다. 수식 입력줄에서 볼 수 있듯이 Text.BeforeDelimiter를 사용 하고 해당 텍스트 문자열의 오른쪽과 왼쪽 부분을 식별하기 위해 해당 공간을 사용했습니다.

공간이 없으면 날짜 열에 남아 있는 모든 항목을 반환하려고 합니다. 따라서 첫 번째 레코드의 경우 1을 반환합니다. 해당 공백이 포함된 두 번째 레코드의 경우 텍스트 문자열의 첫 번째 부분인 Third 단어를 반환합니다 .

비슷한 접근 방식을 사용하여 날짜 이름을 가져오지만 List.PositionOf 함수를 사용합니다 . 이것은 첫 번째 인수로 전달된 목록 값의 0부터 시작하는 오프셋을 반환하고 값이 해당 목록에 나타나지 않으면 -1을 반환합니다.

첫 번째 레코드의 경우 목록에 없는 값 1을 전달합니다. 이것이 -1 값을 반환하는 이유입니다. 두 번째 레코드의 경우 Monday 값을 반환합니다 .

그것은 그것을 우리 함수에 전달하고 우리 목록의 두 번째 항목이기 때문에 1을 반환합니다. 다음으로 모든 -1 값을 null 로 바꿉니다 .

이 시점에서 사용자 지정 함수를 호출하는 데 필요한 모든 빌딩 블록을 만들었습니다.

영구 휴일 달력에 대한 사용자 지정 함수 호출

수식 입력줄에서 함수가 연도 , 월 , 숫자 및 요일 의 네 가지 인수를 취하는 것을 볼 수 있습니다 .

그러나 날짜 값을 어떻게 결정합니까? List.PositionOf를 다시 사용하여 숫자에 대한 숫자 값을 가져옵니다. 첫 번째, 두 번째, 세 번째, 네 번째를 포함하는 목록이 있습니다. 숫자를 List.PositionOf 에 전달하면 0부터 시작하는 오프셋을 반환합니다.

다시 말하지만, 값이 없으면 -1을 반환합니다. 따라서 첫 번째 레코드의 경우 값 1을 전달합니다. 1은 이 목록에 없으므로 -1 값을 반환합니다. 두 번째 레코드의 경우 목록의 세 번째 항목인 Third 값을 전달합니다. 이것은 2의 값을 반환합니다.

날짜 목록 만들기

이제 날짜 목록을 만들어 보겠습니다. 해당 월의 시작 날짜를 결정하고 해당 월의 일 수를 결정한 다음 List.Dates를 사용하여 해당 월의 모든 날짜를 포함하는 목록을 만듭니다.

다음으로 List.Select를 사용하여 특정 요일에 있는 날짜만 유지합니다.

이 목록 옆을 클릭하면 이 네 가지 항목만 포함되어 있음을 알 수 있습니다.

조건부 논리 적용

마지막으로 조건 논리를 적용하여 날짜를 구성하거나 검색합니다. 요일이 null 이면 특정 날짜가 있음을 나타내므로 내장 #date를 사용하여 날짜 값을 구성할 수 있습니다.

숫자가 마지막과 같으면 List.Reverse 함수를 사용하여 목록의 순서를 반대로 하고 첫 번째 값을 추출할 수 있습니다.

이 오름차순을 내림차순으로 변경하려면 해당 날짜 범위에서 항상 마지막 날짜를 추출할 수 있습니다. 목록에 4개 또는 5개의 요소가 있는지는 중요하지 않습니다. 순서를 반대로 하고 첫 번째 항목을 추출하면 항상 마지막 날짜가 표시됩니다.

다른 모든 경우에는 List.Skip 을 사용합니다 .

목록에서 이 위치를 기준으로 값을 추출할 수 있습니다. 추수감사절의 경우 숫자는 4이고 4는 3을 반환합니다. 이렇게 하면 목록의 처음 세 값을 건너뛰고 항상 네 번째 항목을 반환합니다.

모두 정리하고 솔루션 쿼리로 이동하겠습니다. 소스 단계의 경우 RAW 공휴일 날짜 테이블을 호출합니다. 다음으로 날짜가 포함된 목록을 반환하는 열을 추가합니다.

영구 휴일 달력에 대한 M 코드 검토

열에 대한 M 코드를 확인합시다. 테이블의 각 행에 대해 날짜 테이블에서 고유한 연도를 포함하는 테이블을 중첩했습니다. 그런 다음 사용자 지정 함수를 호출하여 해당 연도 열의 값을 변환했습니다.

물론 사용자 지정 함수를 호출하려면 먼저 몇 가지 설정을 수행해야 합니다. 월 번호 , 숫자 값 , 물론 요일 도 가져와야 합니다 . 그런 다음 중첩된 테이블에서 목록을 날짜와 함께 보관했습니다.

흰색 공간의 측면을 클릭하면 아래에서 해당 목록의 미리보기를 볼 수 있습니다.

다음 단계는 해당 목록을 확장하고 CelebratedOnDate 를 추가하는 것입니다 .

이것이 우리가 이 계산에 사용한 논리입니다. 날짜가 토요일이면 하루를 빼서 금요일이 됩니다. 일요일이면 하루를 추가하여 월요일에 도착합니다. 토요일이나 일요일이 아니면 null을 원합니다.

마지막으로 마지막 단계는 유형을 변경하는 것입니다.

결론

LuckyTemplates 포럼에서 매주 첫 번째와 세 번째 수요일에 진행되는 시리즈라는 점을 간단히 알려드립니다 . 1주차에 게시된 문제는 DAX 기반 솔루션이 필요하며, 3주차 문제는 파워 쿼리 기반 솔루션이 필요합니다.

이 POTW에서 다루는 콘텐츠가 마음에 드셨다면 LuckyTemplates TV 채널을 구독하여 자세한 내용을 확인하십시오. 저와 다양한 콘텐츠 제작자가 항상 제공하는 엄청난 양의 콘텐츠가 있으며 모두 LuckyTemplates 및 Power Platform을 사용하는 방식을 개선하는 데 전념하고 있습니다.

여자 이름