학습노트

파워 쿼리 2주 챌린지_2주차 노트

2월 2일 조회 1,121

✅폴더의 파일을 취합하는 방법 2가지

1. 파워쿼리의 기본 기능만 써서 파일취합

(목적 : 파일 취합이 어떻게 동작하는지 원리를 알아보기 위함)

2. 간단한 함수를 써서 파일 취합

(기본 기능만 사용해서는 구현할 수 없었던 다양한 상황의 파일 취합을 훨씬 더 편하게 할 수있다)

틈새 Tip👩‍💻 컨트롤+ n = 새 통합 문서 열기

🔷 1. 파워쿼리 기본 기능만 써서 파일취합

1번 방법으로 파일 취합하는 과정 👇

[데이터]
[데이터 가져오기]
[폴더에서]
[결합 및 다음으로 로드]
샘플 파일을 고르는 이유
: 데이터를 합치는 과정을 나머지 파일에도 적용하기 위함. 샘플 파일의 머릿글을 사용
(즉, 어느정도 동일한 데이터 구조를 가져야 한다는 말 / 머릿글 순서는 중요하지 않음)
(그런데 함수를 쓰면 샘플파일은 중요하지 않음.)

- 폴더에 새로운 파일을 추가해도 표를 새로고침 하면 알아서 취합
- 하위 폴더의 파일도 알아서 취합

다만, 이 파워쿼리 기본 기능만 쓰는 방법 만으로 실무에 적용하기에는 제한 사항이 많다

① 사용자마다 폴더 경로, 폴더이름이 변하는 경우 취합 안됨
② 파일 마다 시트 이름이 다른 경우 취합 안됨
③ 머리글 필드가 자주 변하는 경우
④ 여러개의 폴더에서 취합해야 하는 경우

[데이터] - [쿼리 및 연결] 탭의 내용으로파워쿼리의 파일 병합 기능이 어떻게 동작하는지 알아보기

파일을 취합한다는 것은 기본적으로
데이터 구조가 거의 동일하다는것을 전제로 하는 것이므로,

만약 샘플파일인 A파일에 어떤 쿼리를 적용했다면,
( 그러니까, [쿼리 및 연결] 탭의 내용 )
나머지 파일에도 동일한 쿼리를 적용하여 파일을 취합한다

'아 이렇게 동작하는구나' 라고 이해만 해도 충분

🔷 2. 간단한 함수를 써서 파일 취합

1️⃣ Folder.Files ("폴더경로")
→ 폴더 안의 파일 목록을 반환한다.

2️⃣ Excel.Workbook (엑셀파일)
→ 엑셀 통합 문서 안의 컨텐츠 (시트, 테이블, 등)을 반환한다.

3️⃣ Table.Skip (테이블, 개수 or 조건)
→ 테이블에서 상위n개 행 또는 조건을 만족하는 행을 제거한다.
→ [홈]-[행 제거] 기능을 사용하면 만들어지는 함수

4️⃣ Table. PromoteHeaders (테이블)
→ 첫 행을 머리글로 승격한다.
→ [홈]-[첫 행을 머리글로 사용] 기능을 사용하면 만들어지는 함수

이 함수들만 쓸 줄 알아도 웬만한 파일 취합 모두 가능✅

파워 쿼리 화면에서 표의 머릿글을 바꾸지 않고
함수 부분에서 머리글을 바꾸면
우측 매크로 내용이 추가되지 않기 때문에 간소화 할 수 있다.

2번 방법으로 파일 취합하는 과정👇

[데이터] - [데이터 가져오기] - [폴더에서]
1번 방법과 달리 [결합]버튼 누르지 않음
[데이터 변환]버튼 클릭

폴더에 있는 엑셀 파일들의 데이터를 보여준다.
content 필드를 우클릭해서 [다른 열 제거]
[열 추가] - [사용자 지정 열]
새 열 이름을 지정해주고
= Excel.Workbook 함수입력
괄호열고 content 더블클릭
= Excel.Workbook([content])
[확인] 클릭

이제 content 열은 삭제 해주고,
머리글 부분에 있는 확장 버튼을 클릭해서 [원래 열 이름을 접두사로 사용]을 체크해제.
[확장] 클릭

Data 열 우클릭 - [다른열 제거]
확장 버튼 클릭하여 확장

🚫그런데, 이렇게만 병합하면 문제점이 발생한다

① 머릿글이 중복되어 병합되었음
② 그렇기 때문에 데이터 순서가 꼬임

우리는 각각의 테이블의 머리글을 승격해줘야 한다.

[열 추가] - [사용자 지정 열]
= Table. PromoteHeaders
Data 필드 더블클릭
= Table. PromoteHeaders([Data])

Date 열 지워줌
머리글이 승격된 이 데이터들을 확장해줌

[닫기 및 다음으로 로드]

[쿼리 및 연결]에서,
1번 방법 : 쿼리가 복잡함
2번 방법 : 쿼리가 간결함

데이터가 많아질수록 적용된 단계가 기하급수적으로 늘어나기에,항상 간결하게 유지하는것이 기술👩‍🔬

앞선 단계에서는 취합했으니,
이제 쿼리를 보기좋게 꾸며볼 차례 ✨

[쿼리 및 연결]에서, 쿼리 우클릭 [편집]

ex) 데이터 형식이 날짜가 아니라 텍스트인 [거래일자] 열을 날짜로 바꿔줌

ex) 한 셀에 정보가 2개가 있으면 열분할 시켜줌

ex) 값바꾸기로 필요없는 글자 지우기

ex) 머리글 이름 변경

폴더에 새 파일을 추가하고 쿼리를 새로고침하면 알아서 취합 완료🙆‍♂️

QnA

🙋‍♀️ 취합한 쿼리 행 순서를 변경하고 싶어요.

= 쿼리에서 수정가능!
ex) 거래일자를 오름차순으로 정렬

🙋‍♂️ 쿼리 표가 너무 촘촘해서 보기 힘들어요.

=[테이블 디자인] - [속성]
[열 너비 조정] 체크 해제
새로고침해도 열 너비 초기화 안됨.

🙋‍♀️ 가장 최근의 3개 파일만 취합하고 싶어요

=[적용된 단계]의 원본 탭에서,
원하는 조건의 열을 오름차순이나 내림차순으로 적절하게 정렬하고,
[행 유지] 기능으로 원하는 파일만 유지시킨다.

🙋‍♂️ 파일 안에 시트가 여러 개 있을때 여러개의 시트도 한번에 취합하는 방법?

= 2번 취합과정과 그냥 동일.

🙋‍♀️ 검토중인 파일만 제외하고 취합하고 싶어요.

= (검토중인 파일은 파일이름에 (검토)라고 적혀있다는 전제)
텍스트 필터를 추가해서 (검토)가 없는 시트만 남긴다.
검토가 끝난 후에, 파일이름에서 (검토)를 지워주면 알아서 취합 됨.

🚩

그런데,

1행부터 머리글 부터 시작하는 깔끔한 데이터가 아니라,
어떤 특정 문서 서식이 있어서 상단이 머리글로 시작하지 않는 파일의 경우 어떻게 취합하면 될까?

👉 취합하고자 하는 파일들이 서식이 동일한 경우

머리글을 승격해주는
= Table. PromoteHeaders([Data]) 함수를 쓰기 이전에, 먼저 필요없는 상단 행을 지워주자.

= Table.Skip ([Data],6)
( 해석 : Data에서 6개를 지울래요 )

그런다음 머리글을 승격시켜보자.

그런데 이 방법으로는 적용된 단계가 2개가 추가되니까, 함수를 합성해서 단계를 줄여보자.

머리글을 6개를 지운 단계에서를 더블클릭하여 다음 함수 입력

= Table. PromoteHeaders(Table.Skip ([Data],6))
( 해석 : 6개 스킵 한 다음에 머리글을 올려줄래요)

👉 취합하고자 하는 파일들이 서식이 다르면요?

빈칸 (null)을 지워주는 함수
해당하는 머리글을 필터링하는 함수
(이번 강의에서는 다루지 않음)

🚩

실무에서 파워 쿼리를 처음 공부할때많이 접하는 오류"테이블의 'O' 열을 찾을 수 없습니다."

해결방법:

①
쿼리 및 연결에서 해당하는 표를 오른쪽 클릭 - 편집

②
[적용된 단계] 란에서 어떤 단계에서 오류가 발생했느지를 체크
(70%의 경우에는 변경된 유형에서 발생)

③
변경된 유형 단계는 지워도 괜찮으니 지워준다.
데이터 형식을 바꾸는 과정은 마지막 단계에서만 추가해도 괜찮기 때문이다.

④
마지막 단계에서 [변환] - [데이터 선택 데이터 형식 검색]으로 데이터 유형을 변경한다
(마지막 단계에서 변경된 유형을 추가하게 된다.)

이런 과정을 거치면,
중간에 원본 표의 머리글 이름을 변경해도, 해당하는 오류는 발생하지 않는다.

🚩취합을 진행한 폴더의 이름을 추후에 변경하면,폴더를 찾을 수 없다고 경고문이 뜬다.

폴더를 찾을 수 없다고 경고문이 뜬 쿼리의 원본 단계에서 함수를 보면
이전 폴더이름으로 되어있는것을 확인 할 수 있다.

이때는 M함수를 활용해야 하는데,
먼저 M함수에 대해서 간단히 알아보자.

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

(M=Mapping / 약 800개 이상)

ex)

Folder.Files

Excel.Workbook

Table.Skip

Table. PromoteHeaders

그래서, 함수를 정확히 알지못해도
범주를 보면 대략 어디에서 쓰이는 함수인지 유추할 수 있다

M함수의 기본적인 목적은
어떤걸 찾거나 검색하거나 구조를 바꾸기 위함이다.

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

2️⃣파워쿼리에서 {}는 '행'을 선택하고, []는 '필드'를 선택한다
(주의! 파워쿼리의 배열은 '0'부터 시작한다)

= Excel.CurrentWorkbook(){2}[Content]{3}[가격]

해석:
현재 실행중인 통합문서에서,
3번째 행에 있는 데이터를 불러올건데,
컨텐트 필드를 볼거고,
이 중에서 4번째 행에서 가격필드에
해당하는 값을 불러올겁니다

= Excel.CurrentWorkbook(){2}[Content]{[지역="제주도"]}

해석: 지역을 볼건데, 지역값이 제주도인 행을 불러올겁니다

= Excel.CurrentWorkbook(){2}[Content]{[지역="제주도"]}[가격]

해석: 지역값이 제주도인 행에서 가격필드의 값을 불러올겁니다

단, 그 값이 하나일때만 가능

다시 처음으로 돌아가서,폴더이름을 바꾸는 바람에 취합하는 과정에서 오류가 난 상황으로 다시 돌아와보자.

폴더의 경로를 추출하는 방법 👀
(폴더 이름이 바뀌더라도 취합가능)

해당하는 폴더에 있는 엑셀파일의 빈 시트에 머리글은 경로라고 설정해주고
2열에
=LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1)))

함수를 사용하면 경로가 나온다
(단, 원드라이브 사용시 불가)

이 2개의 셀로 표를 만들어주고,
임의로 표 이름일 Path라고 한다.

옆에 열을 추가하고 머리글은 취합경로.
그리고 그 아래 셀에
=[@경로]&"보고서₩"
(보고서 폴더를 보겠다는 말)

엔터를 누르면 취합할 경로가 만들어짐

폴더이름을 바꿔도 알아서 경로가 수정되어 업데이트됨

이 표로 파워쿼리를 열여서
= Excel.CurrentWorkbook(){Name="Path"}[Content][취합경로]{0}

(Path라는 표에서 취합경로에 있는 1행을 불러옴).

즉, 이 수식을 쓰면 동적으로 반환되는 마지막 취합경로를 받아올 수 있다는 것을 알 수 있음.

이 수식을 복사해서 메모장 실행하여 복붙

왼쪽 쿼리 목록에서 '보고서' 쿼리 선택
[홈]-[고급 편집기]

맨 윗줄에 있는 원본 단계에 앞서는 단계를 추가해주자.
Path = = Excel.CurrentWorkbook(){Name="Path"}[Content][취합경로]{0}

그리고 마지막에 쉼표를 꼭 추가해준다

= Excel.CurrentWorkbook(){Name="Path"}[Content][취합경로]{0},

그리고 다시 원본 단계에서, 괄호에 있는 내용을 지우고,

괄호에 Path를 입력해준다.

원본 = Folder,Files(Path),

[완료] 클릭

오류가 사라지면서 파일취합이 완료

왼쪽 쿼리 목록에서
Path 쿼리는 삭제하여 정리해준다

이제 폴더이름을 바꿔도, 취합에 문제가 없다.

👉 경로를 추출하는데 사용했던 그 엑셀파일이 있는 위치에,

하위폴더가 있으면 모두 취합해주는 것이기 때문에, (보고서 폴더)
그 안에서 경로가 바뀌어도 취합 가능 (보고서 폴더안에 하위폴더로 바뀌더라도)

👉 폴더이름에 상관없는 경로를 받고싶다면,

경로를 받아오기 위해 만들었던 Path표에 '경로' 필드 다음에 열을 추가하여

표를 적절하게 수정해주자.

감사합니다!!