1월 매출목표, 5월생산목표등비교대상지표가월단위인경우가많습니다. 월보다작은단위는설정과평가모두부담스러울수있고, 월보다큰단위는평가의실효성이떨어질수있기때문에 월단위 지표가많을것으로생각됩니다. 다만, 월단위 KPI는분석이제한적일수밖에없습니다. 주간또는일일실적도월간목표대비달성률이측정될수밖에없기때문입니다.
11월 1일 10을달성했다면, 월목표대비약 3.3%를달성했다고계산할수있습니다. 그런데더욱세밀한관리가필요한비즈니스라면이지표를통해서적정성여부를판단하기가어렵습니다. 11월 10일경에누적달성률이 40%라면 잘 하고 있다고판단하고, 15일경에 45%라면다소부족하다고판단할수는있지만당장오늘실적의좋고나쁨을판단하기는어렵습니다.
예제의 [목표] 테이블은엑셀표형식으로되어있습니다. 이표의내부아무곳이나선택한후 [데이터] 탭 > [데이터 가져오기 및 변환] 그룹 > [테이블/범위에서] 를 선택합니다. 테이블을 가져오면 Power Query 편집기가 실행되고 #"원본" 쿼리와 #"변경된 유형" 쿼리가 실행됩니다. #"변경된 유형" 쿼리는 데이터 형식을 자동으로 검색 판단하여 적절한 유형으로 변경하는 것으로 이 단계를 삭제하고 사용자가 판단하여 유형을 설정하는 것이 좋지만, 여기에서는 자동 설정된 '정수' 유형이 적절하므로 그대로 진행합니다.
= Table.TransformColumnTypes(#"이름을 바꾼 열 수",{{"목표 시작일", type date}})
5. 월별일수구하기
'목표 시작일'의 속성을 날짜로 변경했으니 '목표 시작일'로부터 해당 월의 일 수를 구할 수 있습니다. '목표 시작일' 열을 선택하고 메뉴에서 [열 추가] 탭 > [날짜 및 시간에서] 그룹 > [날짜] > [월] > [월간 일자] 를 선택하면 해당 날짜가 속하는 월의 일 수를 나타내는 열이 추가됩니다.
= Table.AddColumn(#"변경된 유형1", "월간 일자", each Date.DaysInMonth([목표 시작일]), Int64.Type)
6. 일 목표 구하기
'목표' 열을 '월간 일자' 열의 값으로 나누면 일간 목표가 산출됩니다. '목표' 열을 선택하고, Ctrl 버튼을 누르고 '월간 일자' 열을 선택합니다. (반드시 '목표' 열을 먼저 선택해야 합니다. 반대로 선택하게 되면 반대의 나눗셈 결과가 구해지므로 주의해야 합니다)
= Table.AddColumn(#"삽입된 월간 일자", "나누기", each [목표] / [월간 일자], type number)
7. 일간 목표 열 이름 바꾸기
'나누기' 열의 이름을 변경합니다. 나누기 열을 선택하고 메뉴에서 [변환] 탭 > [열] 그룹 > [이름 바꾸기] 를 선택합니다. 나누기 열의 제목이 변경가능 상태로 바뀌면 '일 목표' 라고 이력하고 Enter를 눌러줍니다.
여기까지 작성된 쿼리는 매 월 목표가 추가되면 자동으로 해당 월의 일수에 따른 일별 목표를 산출해 줍니다. 물론, 연도가 고려되지 않아서 이에 대한 정의가 추가로 필요합니다. 다만, 이 쿼리를 데이터 모델에 추가하여 다양한 날짜 필터를 적용하기 위해서는 기간에 포함된 날짜별 일 목표가 필요합니다. Power Query의 데이터 형식 중 하나인 목록(List)을 활용하여 날짜를 생성하는 방법을 알아보겠습니다.
8. 목표 종료일 열 추가
월 목표액이 적용될 시작일이 포함되는 열이 설정되어 있으므로 적용 종료일 열을 삽입하겠습니다.
'목표 시작일' 열을 선택한 뒤 [열 추가] 탭 > [날짜 및 시간에서] 그룹 > [날짜] > [월] > [월말] 을 선택합니다.
(열이 추가되고 나면 위 3번 또는 7번 단계를 참고하여 열 이름을 '목표 종료일'로 변경합니다)
= Table.AddColumn(#"이름을 바꾼 열 수1", "월말", each Date.EndOfMonth([목표 시작일]), type date)
= Table.RenameColumns(#"삽입된 월말",{{"월말", "목표 종료일"}})
9. 목표 시작일부터 종료일까지의 모든 날짜를 포함하는 List 열 추가
M 언어에서는 목록(List) 형식을 지원합니다. 목표 시작일부터 목표 종료일까지의 모든 날짜를 포함하는 리스트를 생성해 보겠습니다. 메뉴 [열 추가] > [사용자 지정 열]을 선택하면 직접 쿼리문을 입력할 수 있는 창이 팝업됩니다.
여기에 아래와 같이 쿼리 문을 작성합니다.
{Number.From([목표 시작일])..Number.From([목표 종료일])}
Nuber.From 함수 제공된 Value에서 Number를 반환합니다. 이 예제에서는 날짜 Value인 '목표 시작일'열과 '목표 종료일'열을 제공했기 때문에 엑셀과 마찬가지로 해당 날짜의 본래 데이터인 43831, 43832... 등의 숫자를 반환했습니다.
.. 연산자 첫번째 인수부터 두번째 인수까지 연속되어 있음을 나타내는 연산자입니다. (이에 대한 공식문서를 아시는 분께서는 댓글 부탁드립니다. 도저히 못찾겠어요!)
= Table.AddColumn(#"이름을 바꾼 열 수2", "목표일", each {Number.From([목표 시작일])..Number.From([목표 종료일])})
= Table.ExpandListColumn(#"추가된 사용자 지정 항목", "목표일")
10. '목표일' 열의 List를 새 행으로 확장
'목표일'열 제목의 오른쪽에 있는 아이콘(화살표가 양방향으로 향하는)을 선택하여, [새 행으로 확장]을 선택하면 List가 갖고 있던 값을 모두 새로운 행으로 생성하여 확장시킵니다.
= Table.ExpandListColumn(#"추가된 사용자 지정 항목", "목표일")
11. '목표일' 열 형식을 날짜로 변환
'목표일' 열 제목 좌측의 형식 아이콘을 클릭하여 [날짜]를 선택하여 '목표일' 열의 형식을 날짜로 변경합니다. 형식 변환은 [변환] 탭 > [열] 그룹 > [데이터 형식] 을 선택하여 변경하거나, '목표일'열을 선택하여 마우스 오른쪽 버튼을 눌러서 [날짜]를 선택하여 변환할 수도 있습니다.
= Table.TransformColumnTypes(#"확장된 목표일",{{"목표일", type date}})
12. 로드
모든 과정이 끝났습니다.
이제 결과 확인을 위해서 표로 로드합니다.
[홈] 탭 > [닫기] 그룹 > [닫기 및 로드] > [닫기 및 다음으로 로드...] 을 선택합니다.
[데이터 가져오기] 팝업창에서 [표]를 선택하고, 메뉴 하단에 데이터가 들어갈 위치를 [기존 워크시트]로 선택한 뒤 '월간 목표 테이블' 시트의 E1 셀을 선택합니다.
월 목표지표가 일 목표지표로 일 수에 따라서 안분되었고 또 해당 월의 모든 날짜가 행으로 추가된 결과를 확인할 수 있습니다! 다음 편에서는 생성된 쿼리를 데이터 모델에서 활용하는 방법을 소개하도록 하겠습니다. 감사합니다.
전체 쿼리
let
원본 = Excel.CurrentWorkbook(){[Name="목표"]}[Content],
#"변경된 유형" = Table.TransformColumnTypes(원본,{{"월", Int64.Type}, {"목표", Int64.Type}}),
#"삽입된 병합된 열" = Table.AddColumn(#"변경된 유형", "병합됨", each Text.Combine({"2020-", Text.From([월], "ko-KR"), "-1"}), type text),
#"이름을 바꾼 열 수" = Table.RenameColumns(#"삽입된 병합된 열",{{"병합됨", "목표 시작일"}}),
#"변경된 유형1" = Table.TransformColumnTypes(#"이름을 바꾼 열 수",{{"목표 시작일", type date}}),
#"삽입된 월간 일자" = Table.AddColumn(#"변경된 유형1", "월간 일자", each Date.DaysInMonth([목표 시작일]), Int64.Type),
#"삽입된 나눗셈" = Table.AddColumn(#"삽입된 월간 일자", "나누기", each [목표] / [월간 일자], type number),
#"이름을 바꾼 열 수1" = Table.RenameColumns(#"삽입된 나눗셈",{{"나누기", "일 목표"}}),
#"삽입된 월말" = Table.AddColumn(#"이름을 바꾼 열 수1", "월말", each Date.EndOfMonth([목표 시작일]), type date),
#"이름을 바꾼 열 수2" = Table.RenameColumns(#"삽입된 월말",{{"월말", "목표 종료일"}}),
#"추가된 사용자 지정 항목" = Table.AddColumn(#"이름을 바꾼 열 수2", "목표일", each {Number.From([목표 시작일])..Number.From([목표 종료일])}),
#"확장된 목표일" = Table.ExpandListColumn(#"추가된 사용자 지정 항목", "목표일"),
#"변경된 유형2" = Table.TransformColumnTypes(#"확장된 목표일",{{"목표일", type date}})
in
#"변경된 유형2"