[엑셀 파워쿼리] 두 날짜 사이의 월(Month)을 새로운 행으로 만들기

오늘의 결과물

그림1) 건물별로 집게된 데이터 → 월별로 임차료 정리

※ 해당 예시는 가볍게 보여주기 위하여 데이터 2개 기준으로 보여드립니다.
 
재무팀에서 많이 쓸 법한 내용으로 들고 왔습니다.
왼쪽에는 건물A와 건물B에 대한 계약일자와 만기일자가 있습니다. 하지만 월별 임차료가 얼마인지는 건물이 많아지면 많아질수록 왼쪽과 같은 데이터는 정리하기가 어렵습니다. 
따라서 먼저 왼쪽과 같은 데이터는 tidy한 데이터 형태로 바꿔줘야 하며 tidy data 형태로 바꾸는 방법을 파워쿼리로 알아볼 예정입니다. tidy data로 변경한 뒤에는 그림1)의 오른쪽 형태처럼 피벗테이블로 바꿔서 들고 올 수 있습니다.
 

 파워쿼리로 정리한 tidy 데이터 결과물

그림2) 파워쿼리로 정리한 tidy 데이터 결과물

 
그림2를 보시면 왼쪽의 경우 위와 같은 원본이며, 오른쪽의 경우 '날짜리스트'라는 새로운 열을 만들어서 건물A의 만료일자에서 계약일자 사이에 있는 연월인 23년 8월, 9월, 10월을 가지고 왔습니다(임대료 납부월 기준)
오른쪽처럼 만들어줘야 월별로 건물임차비가 얼마씩 나가는지 한눈에 볼 수 있습니다 (※ 그림1, 오른쪽 참고)
 

파워쿼리 준비하기

저는 현재 Window 엑셀2016 기준으로 사용 중이며, Mac은 24년 1월 기준 파워쿼리는 M365버전만 가능하며 이 외 다른 버전은 안되는 것으로 알고 있습니다. Window 사용자 분 중 2016 이전 버전 중 2010, 2013은 별도 파워쿼리 설치필요하며, 2016 업그레이드 가능하면 하시는 것을 권장드립니다(2016부터는 별도 설치 필요 없음)
 
① 파워쿼리창 이동은 원본데이터에 커서 두신 뒤 '표'로 변경해주시면 됩니다.(단축키 : Ctrl + 'T')
2022.09.25 - [엑셀(Excel)/파워쿼리] - [파워쿼리] 파워쿼리 편집창 이동

[파워쿼리] 파워쿼리 편집창 이동

 

그림3) 표이름 지정

'표' 형태로 만들게 되면 '디자인'이라는 탭이 생깁니다.
② 파란색 박스로 해둔 '임차료'처럼 표 이름을 변경해줍니다(파워쿼리 코드에서 표 이름을 기준으로 읽어옵니다 !)
③ '데이터' 탭에서  '테이블에서'로 파워쿼리 편집창으로 이동합니다.

그림4) 파워쿼리 편집창 이동하기

데이터유형변경

① 데이터 유형변경(datetime → date)

그림5) 데이터 유형변경(datetime → date)

파워쿼리의 경우, 형변환을 자동으로 시행해줍니다.
형변환이란 날짜 데이터 같으면 날짜 데이터로, 문자 데이터 같으면 문자데이터로 변환하는 것을 말합니다.
데이터 유형은 문자, 날짜, 숫자, 논리(True/False) 등을 말합니다.
 
처음 변경된 유형을 보게되면 datetime으로 되어있는데 이는 날짜뿐만 아니라 시분초까지 포함된 개념입니다. 여기서는 시분초는 필요 없으므로 date로 변경하여 연월시만 부르면 된다. 수식탭에서 바로 타이핑하여 변경하면 됩니다.

M함수 : List 및 Number.From 사용한 데이터 변환

파워쿼리의 함수(M함수)를 사용해 볼 예정입니다.
'열추가'에서 '사용자지정 열' 클릭하면 데이터를 불러올 수 있는 창이 있습니다.

※ M함수의 경우 엑셀함수와 다르게 대소문자 구별해서 사용해주셔야 합니다.

그림6) 사용자 지정열 불러오기

List.RemoveFirstN(
 List.Distinct(
 List.Transform(
 {Number.From([계약일자])..Number.From([만기일자])},
 each Date.ToText(Date.From(_), "yyyy-MM")
 )
 ),
 1)

위 함수를 사용하여 하나씩 문제를 해결해 나가면 됩니다.
M함수도 엑셀 함수와 동일하게 중첩이 되며, 해당 함수의 경우 중첩되어 어려워 보일 뿐입니다.
 
먼저 중간에 있는 {Number.From([계약일자])..Number.From([만기일자])} 부분을 살펴보겠습니다.
엑셀의 M함수의 경우 List 데이터를 표현할 때 {1, 2, 3} 처럼 작성할 수 있습니다.
하지만 다른 방법으로는 {1..3}처럼 표현해도 됩니다.
 
이에 착안해서 {[계약일자]..[만료일자]} 처럼 정리하면 일자를 다 불러올 수 있습니다. 
다만 Date타입은 리스트로 부르지 못하기 때문에 Number.From과 같이 숫자형으로 변환하는 함수를 사용합니다.
 
※ M함수 공식문서(Number.From)
https://learn.microsoft.com/ko-kr/powerquery-m/number-from

Number.From - PowerQuery M

 
날짜를 숫자형태로 변환하였기 때문에, 숫자데이터 List가 나타납니다.

M함수 통한 List화 (날짜데이터 숫자 형태)

엑셀의 경우, 날짜데이터가 1900-01-01부터 시작합니다.
List의 맨 위에 나타나는 45110이라는 숫자는 계약일자인  2023-07-03에서 1900-01-01의 일수 차이로 나타나고 있습니다.
 
이제 숫자로 되어있는 함수를 다시 날짜로 반환해줘야 합니다.
이런 경우에는 각 데이터를 변경할 수 있는 함수를 사용하여야 합니다.

M함수 : List.Transform통한 숫자형 → 날짜형 변환

List.Transform의 경우, 이럴 때 쓸 수 있습니다.
 
먼저 List.Transform의 예시를 살펴보겠습니다.

먼저 List.Transform의 예시를 살펴보겠습니다.

데이터는 {1, 2}가 있고 해당 함수를 적용하게 되면 

List.Transform({1, 2}, each _ + 1)

출력결과는 1과 2에 각 1씩 더한 값인 {2, 3}으로 바뀌게 됩니다.

 
그럼 이제 실제 데이터에 적용해 보겠습니다.

List.Transform(
 {Number.From([계약일자])..Number.From([만기일자])},
 each Date.From(_))

 

List.Transform함수를 통한 숫자데이터의 날짜변형

M함수 : 일별 데이터 → 월별 데이터로 그룹화(Date.toText, List.Distinct)

현재까지 데이터를 보게 되면 계약일자에서 만료일자까지 모든 일별 데이터를 가지고 옴을 알 수 있습니다.
하지만 저희가 필요한 건 연월의 데이터입니다.
날짜 데이터의 경우 Date.ToText를 사용하면 됩니다. 
 

■ Date.ToText예시

Date.ToText(#date(2010, 12, 31), "yyyy-MM")

함수 첫 번째 인자에는 날짜데이터를 두 번째 인자("yyyy-MM")에는 변경할 유형을 써주면 됩니다.

출력결과 "2010-12"처럼 나타나며, 문자데이터로 나타납니다.

MM의 경우 대문자로 써줘야 합니다.

 

변경 유형과 관련해서 왜 MM을 써야 하는지는 공식문서 참고 부탁드립니다.

https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

Custom date and time format strings - .NET

 

 
실제 데이터에 Date.ToText를 적용하여 결과를 보겠습니다

M함수로 일자별 데이터를 월별 데이터로 변경

 
이제 2023-07로 연월 데이터를 잘 구했습니다. 중복된 항목만 제거해 주면 될 것 같은데요. 
List의 경우 List.Distinct로 제거 가능합니다. 
 

■ List.Distinct 예시

List.Distinct({1, 1, 2, 3, 3, 3})

출력 결과 : {1, 2, 3}

 
실제 예제에서 써보겠습니다.

M함수로 연월 데이터 중복 제거

 

M함수 : List의 첫 번째 행 날려주기(List.RemoveFirstN)

 
앗 근데 구해놓고 보니 임차기준 3개월만 내면 되는데 4개월을 내는 것처럼 나타납니다..!
이런 경우에는 리스트의 맨 첫 번째 행만 날려주면 될 것 같습니다.

■ List.RemoveFirstN 예시

List.RemoveFirstN({1, 2, 3, 4, 5}, 3)

리스트 데이터가 {1..5}까지 있습니다. 그중 뒤에 오는 3은 앞에 3개를 지워달라는 얘기입니다.

출력 결과 : {4, 5}

 
그럼 거의 다 왔습니다. 마지막 예시에서 List.RemoveFirstN에서 파라미터를 1로 지정해 주면 됩니다. 

List의 첫 번째 행 날려주기

새 행으로 확장

 
마지막으로 날짜리스트 열에 ↔ 화살표를 클릭하여 '새 행으로 확장'을 눌러주면 tidy data가 완성됩니다. 
 

파워쿼리 데이터 불러오기

홈 탭에 '닫기 및 로드'를 통해서 불러오시면 됩니다. 
 

파워쿼리 데이터 피벗테이블로 불러오기 

이제 파워쿼리로 불러온 데이터를 피벗테이블로 변경해서 쓰시면 됩니다.
피벗테이블로 불러오는 방법은 파워쿼리 데이터에 마우스 커서를 두시고, 
'삽입'탭 → '피벗테이블' 사용하여 불러옵니다.

피벗테이블로 불러오기

 

고급편집기 활용하시는 분을 위한 전체 코드

파워쿼리 고급편집기

파워쿼리의 강점은 코딩과 비슷하게 남들이 만들어놓은 코드를 복사해서 쓸 수 있다는 점입니다. 
'고급편집기'에서 해당 코드 복사해서 쓰세요 ~ 
 

let
    원본 = Excel.CurrentWorkbook(){[Name="임차료"]}[Content],
    #"변경된 유형" = Table.TransformColumnTypes(원본,{{"건물명", type text}, {"월임차료", Int64.Type}, {"계약일자", type date}, {"만기일자", type date}}),
    #"추가된 사용자 지정 항목" = Table.AddColumn(#"변경된 유형", "날짜리스트", each List.RemoveFirstN(
 List.Distinct(
 List.Transform(
 {Number.From([계약일자])..Number.From([만기일자])},
 each Date.ToText(Date.From(_), "yyyy-MM")
 )
 ), 
 1)),
    #"확장된 날짜리스트" = Table.ExpandListColumn(#"추가된 사용자 지정 항목", "날짜리스트")
in
    #"확장된 날짜리스트"

 파워쿼리로 자동화하기

파워쿼리의 최고강점은 자동화입니다.
데이터가 추가된다고 하더라도 '새로고침'만 눌러주면 됩니다.
건물C라는 데이터를 추가해 보겠습니다.
 

파워쿼리로 자동화 GIF

 
엑셀 자동화로 업무 편하게 하세요 ~