Microsoft Edge로 유데미에 접속합니다. 1. Edge의 페이지 번역 기능을 활성화합니다. 2. player의 자막 메뉴 맨 아래 자막 설정에서 ‘Display under video(영상 아래 표시)’를 활성화 합니다. 3. 번역 완료 자막을 포함해 페이지의 거의 모든 내용이 한글로 번역되어 표시됩니다.
Udemy에는 정말 좋은 강의가 많습니다. 이유는 모르겠으나 자동번역조차도 한글이 지원되지 않는 강의가 있어서 안타까울 때 이 방법으로 수강이 가능합니다.
입력(하드코딩)된 날짜 데이터는 입력된 셀의 내용 모두 검색 대상입니다. 따라서 '찾기 및 바꾸기'를 활용해서 특정 일자를 찾을 수 있습니다. 낱개의 숫자도 검색할 수 있고, 심지어 하이픈(-)도 검색할 수 있습니다. 'Ctrl + ;'를 입력하며 오늘 날짜가 입력되는데 이것 역시 '찾기 및 바꾸기'에서 쉽게 찾을 수 있습니다.
b. 수식이나 함수로 반환된 날짜 찾기
날짜를 직접 입력하지 않고 수식이나 함수로 날짜를 반환시킬 수 있습니다. 대표적으로 TODAY 함수를 이용하면 오늘 날짜가 반환되는데 이렇게 반환된 날짜 값은 찾아지지 않습니다. 간단한 수식으로 날짜를 연산할 수 있는데 이 수식 결과 반환된 날짜 역시 찾아지지 않습니다. 이렇게 수식이나 함수로 입력된 날짜를 찾을 때는 찾기 옵션에서 '찾는 위치'를 [값]으로 변경하면 찾을 수 있습니다. 당연히 직접 입력한 날짜 역시 값이기 때문에 검색 결과에 포함됩니다.
2. 날짜 필터링
필터는 내가 보고자 하는 데이터에 집중할 수 있도록 데이터를 걸러내는 기능 정확하게 날짜 형식으로 된 데이터는 다양한 필터 기능을 활용할 수 있습니다.
a. 날짜 계층 선택
날짜 데이터는 엑셀에서 자동으로 날짜 계층을 구성합니다. 실제 데이터는 YYYY-MM-DD 의 날짜 형식인데, 연 > 월 > 일의 계층 구조로 만들어서 이를 그룹으로 선택할 수 있습니다. 특정 연도와 월을 묶어서 선택해서 필터링할 수 있습니다. 물론, 개별 특정 날짜를 선택하여 필터할 수도 있습니다.
b. 날짜 필터
특정 날짜의 이전, 이번 주, 다음 분기, 내년 등 연속된 데이터인 날짜의 특징을 활용한 다양한 옵션을 설정해서 필터링 할 수 있습니다.
c. 날짜 검색창
엑셀 필터에서는 내용을 검색할 수 있는데 날짜 데이터는 날짜 검색창을 이용할 수 있습니다. 필터 검색창 오른쪽의 작은 화살표를 눌러서 검색 범위를 설정할 수 있습니다. 날짜 검색창을 이용하면 '2010년대의 날짜만', '모든 연도의 3월만', '모든 날짜에서 11일만 검색' 등의 작업을 할 수 있습니다.
개발자 분들이 개발하면서 가장 스트레스 받는 일 중 하나가 이름정하기라는 이야기를 접한 적이 있었는데, Power BI로 작업하면서 그것이 어떤 의미인지 절실히 느낄 수 있었습니다. 계산열과 측정값이 수십개에 달하게 되니 직관적으로 이름을 만들거나 수식을 구성하지 못한 경우에는 사용할 때마다 어떤 값을 사용해야 하는지 다시 고민해야 하는 경우가 발생하고, 정확하지 못한 결과를 만들 때도 있었습니다.
저자는 본 도서의 독자들을 위해서 정의하였지만, 이는 원칙적인 몇 가지 규칙을 익힘으로써 불필요한 시간 낭비를 없애고 작업의 효율을 높이는데 도움될 수 있는 광역적인 내용으로 생각되어 옮겼습니다.
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"