아파트시계열 다루기(4):Power Query M으로 증감테이블 만들기

들어가며

이번 포스팅에서는 전처리작업으로 완성한 매매지수,전세지수 테이블을 활용해서 매매증감,전세증감 테이블을 만드는 Power Query M에 대해서 살펴보도록 하겠습니다. 기존 Excel시계열에는 매매증감 및 전세증감 시트가 존재하지만, 이렇게 각각 지수 테이블을 활용해서 증감 테이블을 별도로 생성하여야 하는 두 가지 이유가 있습니다.

첫 번째 이유는 월간 시계열 원본에는 증감 시트 자체가 별도로 존재하지 않기 때문에 어차피 나중 전처리 작업시 지수 테이블을 바탕으로 별도의 증감 테이블을 생성해 주어야 하기 때문입니다.

두 번째 이유로는 이렇게 증감(변동률) Data는 지수Data에서 파생되는 Data입니다. 따라서 이렇게 지수Data에서 증감Data를 생성해 나가는 과정을 통해서 해당 시계열Data에 대한 이해도를 높일 수 있기 때문입니다.

우선 매매지수, 전세지수 테이블로 각각 매매증감,전세증감 테이블을 생성하기 위해서는 아래 주간인덱스 라는 테이블 생성 작업이 반드시 선행되어야 합니다.

아직 이전 포스팅을 읽지 않으신 분들은 아래를 참고하시기 바랍니다.

아파트시계열 다루기(3):전세지수 전처리작업

아파트시계열 다루기(1):ROOT폴더와 Raw-Data준비

아파트시계열 다루기(2):전처리작업 및 매개변수

그리고 아래 Power Query M 언어로 전처리하는 방법은 별도의 글로 포스팅하도록 하겠습니다.

Power Query M 언어 공식문서 한글 튜토리얼

https://learn.microsoft.com/ko-kr/powerquery-m/

주간인덱스 만들기 Power Query M

기존 테이블 복제 하기

우선 매매증감 테이블을 아래 이미지 처럼 복제 합니다.(굳이 매매증감 테이블이 아닌 다른 테이블이라도 괜찮습니다.)

복제한 테이블 이름을 더블 클릭하여서 주간인덱스라고 테이블 이름을 변경합니다.

주간인덱스 만들기 Power Query M 코드

아래 코드를 Copy Code 합니다.

let
    원본 = Excel.Workbook(File.Contents(ROOT폴더경로), null, true),
    #"3.매매지수_Sheet" = 원본{[Item="3.매매지수",Kind="Sheet"]}[Data],
    #"승격된 헤더" = Table.PromoteHeaders(#"3.매매지수_Sheet", [PromoteAllScalars=true]),
    #"변경된 유형" = Table.TransformColumnTypes(#"승격된 헤더",{{"아파트 매매가격지수  Apartment Purchase Price Indices                                                 (2022.1.10=100.0)", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type text}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}, {"Column71", type any}, {"Column72", type any}, {"Column73", type any}, {"Column74", type any}, {"Column75", type any}, {"Column76", type any}, {"Column77", type any}, {"Column78", type any}, {"Column79", type any}, {"Column80", type any}, {"Column81", type any}, {"Column82", type any}, {"Column83", type any}, {"Column84", type any}, {"Column85", type any}, {"Column86", type any}, {"Column87", type text}, {"Column88", type any}, {"Column89", type any}, {"Column90", type any}, {"Column91", type any}, {"Column92", type any}, {"Column93", type any}, {"Column94", type any}, {"Column95", type any}, {"Column96", type any}, {"Column97", type any}, {"Column98", type any}, {"Column99", type any}, {"Column100", type any}, {"Column101", type any}, {"Column102", type any}, {"Column103", type any}, {"Column104", type any}, {"Column105", type any}, {"Column106", type text}, {"Column107", type text}, {"Column108", type text}, {"Column109", type any}, {"Column110", type any}, {"Column111", type any}, {"Column112", type any}, {"Column113", type any}, {"Column114", type any}, {"Column115", type any}, {"Column116", type any}, {"Column117", type any}, {"Column118", type any}, {"Column119", type any}, {"Column120", type any}, {"Column121", type any}, {"Column122", type any}, {"Column123", type any}, {"Column124", type any}, {"Column125", type any}, {"Column126", type any}, {"Column127", type any}, {"Column128", type any}, {"Column129", type any}, {"Column130", type any}, {"Column131", type any}, {"Column132", type any}, {"Column133", type any}, {"Column134", type any}, {"Column135", type any}, {"Column136", type any}, {"Column137", type any}, {"Column138", type any}, {"Column139", type any}, {"Column140", type any}, {"Column141", type any}, {"Column142", type text}, {"Column143", type text}, {"Column144", type any}, {"Column145", type any}, {"Column146", type text}, {"Column147", type any}, {"Column148", type any}, {"Column149", type any}, {"Column150", type any}, {"Column151", type any}, {"Column152", type any}, {"Column153", type any}, {"Column154", type any}, {"Column155", type text}, {"Column156", type text}, {"Column157", type any}, {"Column158", type any}, {"Column159", type any}, {"Column160", type any}, {"Column161", type any}, {"Column162", type any}, {"Column163", type any}, {"Column164", type any}, {"Column165", type any}, {"Column166", type any}, {"Column167", type any}, {"Column168", type any}, {"Column169", type any}, {"Column170", type any}, {"Column171", type any}, {"Column172", type any}, {"Column173", type any}, {"Column174", type text}, {"Column175", type text}, {"Column176", type any}, {"Column177", type any}, {"Column178", type any}, {"Column179", type any}, {"Column180", type any}, {"Column181", type any}, {"Column182", type any}, {"Column183", type any}, {"Column184", type any}, {"Column185", type any}, {"Column186", type any}, {"Column187", type text}, {"Column188", type text}, {"Column189", type any}, {"Column190", type any}, {"Column191", type any}, {"Column192", type any}, {"Column193", type any}, {"Column194", type any}, {"Column195", type any}, {"Column196", type any}, {"Column197", type any}, {"Column198", type any}, {"Column199", type any}, {"Column200", type any}, {"Column201", type any}, {"Column202", type any}, {"Column203", type any}, {"Column204", type any}, {"Column205", type any}, {"Column206", type any}, {"Column207", type any}, {"Column208", type any}, {"Column209", type any}, {"Column210", type any}, {"Column211", type any}, {"Column212", type any}, {"Column213", type any}, {"Column214", type any}, {"Column215", type any}, {"Column216", type any}, {"Column217", type any}, {"Column218", type any}, {"Column219", type any}, {"Column220", type any}, {"Column221", type any}, {"Column222", type any}, {"Column223", type any}, {"Column224", type any}, {"Column225", type any}, {"Column226", type any}, {"Column227", type any}, {"Column228", type any}, {"Column229", type any}, {"Column230", type any}, {"Column231", type any}, {"Column232", type any}, {"Column233", type any}, {"Column234", type any}, {"Column235", type any}, {"Column236", type any}, {"Column237", type any}, {"Column238", type any}, {"Column239", type any}, {"Column240", type any}, {"Column241", type any}, {"Column242", type any}, {"Column243", type any}, {"Column244", type any}, {"Column245", type any}, {"Column246", type any}, {"Column247", type any}, {"Column248", type any}, {"Column249", type any}, {"Column250", type any}, {"Column251", type any}, {"Column252", type any}, {"Column253", type any}, {"Column254", type any}, {"Column255", type any}, {"Column256", type any}}),
    #"제거된 다른 열 수" = Table.SelectColumns(#"변경된 유형",{"아파트 매매가격지수  Apartment Purchase Price Indices                                                 (2022.1.10=100.0)"}),
    #"제거된 상위 행 수" = Table.Skip(#"제거된 다른 열 수",2),
    #"이름을 바꾼 열 수" = Table.RenameColumns(#"제거된 상위 행 수",{{"아파트 매매가격지수  Apartment Purchase Price Indices                                                 (2022.1.10=100.0)", "주간"}}),
    #"변경된 유형1" = Table.TransformColumnTypes(#"이름을 바꾼 열 수",{{"주간", type date}}),
    #"제거된 오류 수" = Table.RemoveRowsWithErrors(#"변경된 유형1", {"주간"}),
    #"필터링된 행" = Table.SelectRows(#"제거된 오류 수", each [주간] <> null and [주간] <> ""),
    #"추가된 인덱스" = Table.AddIndexColumn(#"필터링된 행", "인덱스", 1, 1, Int64.Type)
in
    #"추가된 인덱스"Copy Code

고급편집기 복사한 Power Query M 코드 넣기

주간인덱스 테이블 마우스 우클릭 고급편집기를 열어줍니다.

고급편집기 팝업이 열리면 코드가 작성된 부분에 마우스 클릭 한 다음, ctrl+A ctrl+V 순서로 단축키를 실행합니다. 끝으로 완료 버튼을 클릭합니다.

이렇게 주간인덱스 테이블에서 고급편집기에 코드를 적용하면 아래 처럼 테이블 구조와 적용된단계가 기존과 다르게 나타나는 것을 확인할 수 있어요.

매매증감 만들기 Power Query M

기존 테이블 복제하기

매매증감 테이블을 만들기 위해서 역시 기존 테이블을 복제합니다.

복제된 테이블 이름을 매매증감 이라고 이름을 변경한 다음, 역시 고급편집기를 열어줍니다.

매매증감 만들기 Power Query M 코드

역시 아래 코드를 Copy Code 합니다.

https://gist.github.com/yangyunho/6374d2698dfefb8b5ae4863e29c5772c

고급편집기 복사한 Power Query M 코드 넣기

역시 마찬가지로 앞서 설명드렸던 방법으로 코드를 붙여넣기 합니다. 그런 다음 완료 합니다.

이렇게 매매증감 테이블이 생성되었습니다.

전세증감 만들기 Power Query M

기존 테이블 복제하기

아래 이미지 예시에서는 전세지수 테이블을 복제합니다. 하지만 굳이 전세지수 테이블이 아닌 다른 테이블을 복제하여도 상관없습니다. 이렇게 기존 테이블을 복제합니다.

복제된 테이블의 이름을 전세증감 으로 변경합니다.

전세증감 만들기 Power Query M 코드

역시 아래 코드를 Copy Code 합니다.

https://gist.github.com/yangyunho/f6c2e979bd7599f43a86166e2635dd7a

고급편집기 복사한 Power Query M 코드 넣기

역시 이전과 같은 방법으로 고급편집기를 열어서 위 복사한 Power Query M 코드를 붙여넣기 합니다. 이렇게 고급편집기 코드 복붙 작업이 완료되면 아래와 같이 나타나게 될 것 입니다.

파워쿼리 편집기 작업내용 Power BI 적용하기

이렇게 필요한 파워쿼리편집기 작업들을 완료한 다음 파일>닫기및적용 합니다.

아래 그림 처럼 파워쿼리편집기 창이 닫히면서 Power BI 에서 로드 팝업창이 뜨면서 적용되는 모습을 볼 수 있습니다.

로드 작업이 완료된 다음 저장 버튼을 눌러서 이제 까지 모든 작업 내용을 저장합니다.

마치며

이렇게 ROOT폴더의 kb_week.xlsx파일의 매매지수와 전세지수를 불러와서 각각 전처리하고, 이어서 각각 테이블을 활용하여 매매증감,전세증감 테이블을 만들어 보았습니다. 물론 매매증감, 전세증감 테이블을 만들기 위해서 주간인덱스 테이블도 만들어 보았습니다. 이 후 포스팅에서는 이제 까지 만들었던 테이블을 가지고 본격적인 분석을 해보도록 하겠습니다. 가장 먼저 매매전세 콤보차트를 그려보도록 하겠습니다.