파워쿼리 기초 완성, 2주 특별 챌린지 - 2주차

클릭 10번으로 끝내는 파일 취합 자동화!!

🔥 미션 목표 :

• 파워쿼리 업무 자동화의 핵심 기능인 '파일 병합' 기능의 동작 원리를 이해합니다.

• M함수의 기초 사용법을 알아본 후, 간단한 함수 구성으로 파일 취합 자동화 쿼리를 작성합니다.

• 실무에서 발생하는 여러 상황에 바로 적용할 수 있는 파일 취합 활용 예제를 알아봅니다.

• 폴더 경로를 동적으로 인식하여 파일을 100% 자동으로 취합하는 자동화 쿼리를 작성합니다.

• 초보자도 함수 5개로 끝내는, 파일 취합 100% 자동화 쿼리 만들기

- 1교시
a. 파일 병합 기초
- 원클릭! 파일 취합 자동화 서식 만들기
- 파일 취합 동작 원리 이해하기

b. M함수 기초
- 함수 3개면 OK! 파일 취합 쿼리 간소화하기
- 머리글이 포함된 보고서 취합하기

* 취합하려는 파일이 열려있으면 취합이 안 됨

데이터 - 데이터 가져오기 - 파일에서 - 폴더에서 - 결합 말고 앞으로는 '데이터 변환' 사용하기!!!!!

Content 열 이외 다른 열 제거 - 열 추가 - Excel.Workbook([Content])

결합 및 다음으로 로드 - 기존 워크시트 또는 새 워크시트

⚠ 엑셀 파일을 합친다? = 적어도 데이터 구조가 같거나 비슷할 때 가능하다.

 

데이터 탭 - 쿼리 및 연결

 

 

* 함수 3개만 기억하자!

 

1) Excel.Workbook (엑셀파일 내 컨텐츠 반환)

 

2) Table.Skip (상위 행 지우기)

 

3) Table.PromoteHeaders (머리글 승격)

 

 

열 제목을 한글로 입력할 때 오류가 난다면 ➡ 쿼리 '고급 편집기' 사용하기

 

 

쿼리 표를 새로고침하면 표 너비가 자동으로 조정되는게 싫다면? ➡ 테이블 디자인 - 속성 - '열 너비 조정' 체크 해제

 

- 2교시
a. M함수 핵심 기호 2가지 살펴보기
b. 폴더 경로를 실시간으로 받아오는 엑셀 공식
c. 파일 취합 100% 자동화 쿼리 만들기

 

M = Mapping

① 파워쿼리 M 함수는 함수 앞에 범주를 작성한다.

예) Table.PromoteHeaders → 테이블 관련 함수이고, 머리글을 승격하겠구나!
예) Excel.Workbook → 엑셀을 참조하는 함수이고, 통합문서 데이터를 불러오겠구나!
예) DataTime.Time →날짜/시간 관련 함수이고, 날짜/시간에서 시간을 반환하겠구나!

★주의! 파워쿼리 함수는 대소문자를 인식하므로, 반드시 대소문자를 정확하게 작성해야 함!

② 파워쿼리에서 중괄호 {}는 '행'을 선택하고, 대괄호는 []는 '필드'를 선택한다. 순번은 0부터 시작한다.
(단, 파워쿼리의 배열은 '0' 부터 시작하는 것만 주의해서 사용)

현재 통합문서의 폴더 경로 반환
=LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1)))

(현재 원드라이브 경로는 사용 불가)

✍ 스스로 점검하기 :

이번 수업에서 알아본 내용을 아래에 제시된 목차에 따라 자유롭게 복습합니다.

• 아래 기본 함수를 사용해, 파일 취합 자동화 쿼리를 작성하는 방법을 단계별로 작성합니다.

① Folder.Files ("폴더경로")
② Excel.Workbook (엑셀파일)
③ Table.Skip (테이블, 개수)
④ Table.PromoteHeaders (테이블)

 

• M 함수에서 "중괄호{}"는 행을 선택하고, "대괄호[]"는 필드를 선택합니다. 아래 표에서 "지역이 제주도인 제품의 가격"을 출력하기 위해 작성한 수식의 동작 원리를 간략히 작성합니다.

=테이블{[지역="제주도"]}[가격]

 

• 이번 2주 특별 챌린지에서 배운 내용 중, 가장 기억에 남거나 도움이 됐던 내용을 자유롭게 기술합니다.

엑셀을 20년 넘게 쓰면서 파워쿼리 편집기를 건드릴 생각을 해보지 않았던 게 후회된다. 생각보다 훨씬 직관적이고 다양하게 활용할 수 있다는 것이 놀라웠다. 어쩌면 앞으로 엑셀 함수보다 더 많이 사용하지 않을까 한다. M함수에 대해서는 조금 더 복습을 해봐야 겠지만, 취합 경로라든가 추출되는 정보를 내가 원하는 대로 동적으로 자동화할 수 있다는 것이 매우 놀라웠다. 오빠두엑셀 감사합니다!!!