파워 쿼리와 VLookup 비교(3)

1. 지점명이 일치하는 모든 매출현황 추출하기

매출자료(파워쿼리)1.xlsx

0.02MB

 

이번에는 매출일자는 빼고 지점명만 일치하는 모든 매출일자 및 매출액을 추출해 보겠습니다.

파워 쿼리와 VLookup 비교(1)과 (2)는 아래 URL을 참고 바랍니다.

 

https://lsw3210.tistory.com/472

https://lsw3210.tistory.com/473

 

2. 쿼리 및 연결 창 열기

위 파일을 열면 엑셀 오른쪽에 쿼리 및 연결 창이 보이지 않는데, 보이게 하려면 데이터 탭의 쿼리 및 연결을 누르거나, 쿼리 탭의 편집 명령을 누르면 됩니다.

 

그러면 오른쪽에 쿼리 및 연결 창이 열리는데

표1과 2는 연결 전용이고, 병합1만 1 개행이 로드되었다고 합니다.

 

병합을 다시 해야 하니

표2를 더블 클릭하거나 마우스 오른쪽 버튼을 누른 후 편집 메뉴를 눌러 파워 쿼리 편집기를 엽니다.

 

파워 쿼리 편집기가 열리면 표2가 선택되어 있는 상태에서 쿼리 병합 아래 '쿼리를 새 항목으로 병합'으로 클릭합니다.

그리고, 아래 콤보 박스에서 표1을 선택하고, 지점명만 일치하면 되므로, 표2와 표1의 지점명을 클릭합니다.

 

그런데 맨 아래 메시지가 "1/1개의 테이블과 일치합니다"라고 되어 있어서 별 도움이 안 되지만 확인 버튼을 누릅니다.

그러면 지점명, 매출일자, 매출액, 표1이 표시되는데, 매출일자와 매출액은 표1에서 가져오면 되므로 매출일자를 클릭하고, Ctrl 키를 누른 상태에서 매출액을 클릭해서 2개 열을 선택한 다음 Delete키를 눌러 지웁니다.

 

그러면 매출일자와 매출액 열이 지워지는데 빨간 선으로 표시한 부분을 보면

= Table.RemoveColumns(원본,{"매출일자", "매출액"})라고 표시되는데 이것이 M언어입니다.

 

글자 그대로 해석하면 "원본 테이블에서 매출일자와 매출액 열을 제거하라"입니다. 원보이란 용어가 생소하고, VBA에서는 Columns("B:C").Delete라고 표현하므로 다릅니다.

이제 표1 오른쪽의 펼침 아이콘을 누른 후 지점명만 체크를 지우고, 그 아래 '원래 열 이름을 접두사로 사용'의 체크도 지우고 확인 버튼을 누릅니다.

 

그러면 지점명 오른쪽에 표1의 매출일자와 매출액이 추가됩니다.

= Table.ExpandTableColumn(#"제거된 열 수", "표1", {"매출일자", "매출액"}, {"매출일자", "매출액"})

 

M언어는 Expand까지는 알겠는데, #"제거된 열 수", 그리고, 매출일자와 매출액이 왜 2번씩 표현되는지 헷갈립니다.

천천히 익숙해질 겁니다.

 

먼저, ExpandTableColumn은 테이블의 열을 확장하라는 뜻입니다. 계속 Table이라는 단어가 사용됩니다. Table(우리 말로는 표)이 매우 중요한 듯합니다.

 

파워 쿼리 편집기 오른쪽의 쿼리 설정 창을 보면 적용된 단계에 '제거된 열수'가 있습니다. 이것이 #"제거된 열 수"가 된 것이고,

 

{"매출일자", "매출액"}, {"매출일자", "매출액"} 여기서 앞의 매출일자, 매출액은 원래 열 이름이고, 두 번째 매출일자와 매출액은 표시할 열 이름입니다.

따라서, 두 번째 매출일자를 매출일로 바꾸면 표시되는 열 이름이 매출일로 바뀝니다.

 

이제 엑셀 표로 보내기 위해 '닫기 및 로드' 아래 '닫기 및 다음으로 로드'를 클릭합니다. 이때 '닫기 및 로드'를 누르면 선택 창 없이 새로운 워크시트에 표가 바로 추가되기 때문에, '닫기 및 다음으로 로드'를 눌러서 창을 띄워서 옵션을 조정하기 위해서 그런 것입니다.

 

'닫기 및 다음으로 로드'를 누르면 아래와 같이 표와 새 워크시트가 선택된 상태인데, 기존 워크시트의 E4셀로 변경합니다. 그리고, 확인 버튼을 누르면

 

그러면 A지점에 해당하는 데이터 5개가 표시되는데, 왼쪽 표1과 비교하면 5개에 매출일과 매출액 모두 맞습니다. 위에서 매출일자를 매출일로 표시하도록 수정해서 병합 결과가 매출일로 표시되고 있습니다.

 

매출자료(파워쿼리)2.xlsx

0.02MB