오빠두엑셀 진짜쓰는 실무엑셀 파워쿼리 챌린지 2주차 후기

정리를 해야 하는데 오늘은 영화를 보러 와서 그냥 필기 노트만 올리는 중. 하하.. 미래의 내가 정리하겠지?

데이터 > 데이터가져오기 > 파일에서 > 폴더에서> 열기 > 쭉 나오면 결합버튼 누르기(버튼 안보이면 이따 함수에서 쓸 수 있음) > 결합 및 다음으로 로드 > 파일 병합

파일을 합친다는 것의 전제조건은
최소한 데이터가 같은 구조거나 비슷한 데이터를 가지고 있을 때 합칠 수 있음
그래서 샘플 파일을 참고하여 만드는 것! 여러개의 파일 중 대표적으로 샘플로 볼 파일을 고르는 것.
기본적으로는 첫번째 파일을 고르면 됨 근데 열 개수가 샘플보다 많은 경우가 있으면 반영 안됨
샘플 선택 > 시트 선택 > 오른쪽에 데이터 보임
이상 없으면 확인 버튼 클릭, 데이터 가져오기 > 표형태 > 기존 시트로 불러오기 하면 다 취합되어 불러와짐

폴더에 새 파일 넣고 쿼리 우클릭 > 새로고침 누르면 새로운 파일도 추가됨. 복붙 안해도 자동으로!

이 표를 참고해서 피봇이나 차트를 만들면 데이터가 쌓일 때마다 업데이트되는 보고서를 만들 수 있다.

머릿글 순서는 중요하지 않음 열 이름에 맞게 들어감

상위 폴더에 하위 폴더들을 넣어도 취합 가능

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

기본적인 파일 취합은 할 수 있지만 실무에 적용하려면 좀 더 배워야 함

사용자마다 폴더 경로가 다름!
2022년 거래내역 폴더명을 2023년 거래내역으로 바꾸면 오류 나옴..ㅠ 폴더 경로까지 인식해서 취합하는 걸 알아볼 예정

파일마다 시트 이름이 다른 경우에도 취합이 안됨
1월 시트명이 거래내역이고 2월 시트명이 2월 거래내역이라면 오류ㅠㅠ

머리글 필드가 자주 변하는 경우에도 오류.
고객사 매출 영업이익을 보다가
이후 고객사 매출 영업이익 성장률까지 본다면
첫번째 샘플 파일에 머리글이 3개라면 성장률 볼 수 없음. 취합이 안됨! 샘플 파일을 모든 머리글을 포함한 것으로 하거나 함수 사용

폴더명 시트명☆

폴더 하나 취합했는데 쿼리가 우측에 쭉 쌓이는데 10개만 취합해도 50개 쌓임

원본 파일에 직원(지점) / 매출 이런식으로 되어 있으면 샘플에 열 나누는 쿼리를 적용하고 (도우미 코드) -> 모든 파일에 같은 쿼리 적용 -> 하나로 취합

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

기능을 함수로 쓸 건데 3개만 쓸 거임
3개만 써도 실무 다 가능

표 > 머리글 미포함 > 확인해서 표로 변경
표> 데이터 > 테이블 범위에서(파워쿼리)
변경된 단계에서 삭제 하고 ㄱㄱ
머리글 보니 셀병합 있어서 > 채우기 > 아래로
첫줄 지우기 홈탭 > 행제거 > 상위 1개

위에 있는 행 지우는 작업
Table.skip -> 상위 데이터 행 지우는 거네

홈탭 > 첫행을 머리글로 사용
Table.Promote 어쩌구 하니까 첫행을 머리글로 쓰는군

제품을 제외한 나머지 변환 > 열피봇해제 > 다른열
Table.unpivotothercolumns
머리글이 바뀌는 걸 볼 수 있다.

직접 하면 단계가 늘어나는데 함수에서 바뀌면 머리글 변경 단계 생략 가능!

파일 > 닫기 및 다음으로 로드 > 기존시트에 붙이기

기존 열 4를 머리글 4라고 바꾸면 오류남..ㅠ
해결법☆
쿼리 우클릭 > 편집 > 단계를 보면 변경된 유형에서 오류가 발견되었다. > 오류 설명 보면 왜 오류 났는지 알 수 있음.
함수 보면 열4 -> 데이터 받아오는 거라 머리글4를 바꾸면 ㄱㅊ

오류의 70%는 "변경된 유형에서"에서 발생. 그래서 이거 없이 고고 해도 됨
마지막 데이터 쭉 선택 후 형식 변경해도 됨
머리글 이름 바꿔서 새로고침해도 오류 x

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
Ctrl+N
데이터 > 데이터 가져오기 > 파일에서 > 폴더에서 > 열기 (폴더 여러개면 상위폴더 클릭)

데이터 변환 버튼 클릭 > 파워쿼리 편집기가 실행된다.

원본에서는 여러개 파일을 볼 수 있음
각 바이너리 클릭 > 파일 시트 정보를 볼 수 있음

콘텐트 필드 우클릭 > 다른 열 제거 (파일 합칠 거거든)

열 추가 > 사용자 지정 열 추가 > 새열이름 Excel.Workbook((content))
혹은
ExcelWorkbook -> 탭 하면 점 찍힘 그리고 컨텐츠

엑셀 시트 목록을 표로 볼 수 있음
첫 열(엑셀목록 콘텐츠) 없애고 시트 목록만 보기
확장 버튼 클릭 > 원래 열이름 접두사로 사용 체크해제 > 확장

데이터 빼고는 우클릭 다른열 제거 > 확인버튼 클릭해서 펼치면 모든 파일 병합 끝☆

여기서 문제는
1) 머리글이 계속 나온다는 문제
2) 실제 머리글이 아니고 column1 2 이런식으로 나옴
파일 취합 시에는 머리글을 참고해야 하는데 column1 2 이런 상태에서 병합하면 열 순서가 꼬여서 병합됨

그래서 합치기 전 각 테이블의 머리글을 승격해야 함

Table.promoteheaders
열 추가 > 열이름 입력
= Table.Promoteheaders((Data))
(데이터필드 더블클릭) > 확인하면 머리글 다 올라감

머리글이 승격된 데이터를 확장하면 데이터 병합 끝

파일 > 닫기 및 다음으로 로드 > 기존 시트

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

쿼리 하나로 해결☆ 이게 중요한 이유는 파일 취합 몇 번 하면 꽉 차버림..ㅠㅠ 쿼리 간소화 중요

쿼리 우클릭 편집

거래일자는 날짜인데 문자로 되어 있으니까
데이터 형식을 날짜로 바꾸면 오류가 나옴
-> 날짜 형식으로 데이터를 바꾸어야 함
작은 따옴표를 ' 마침표 . 로만 바꾸몬 날짜 형태

찾을 값 ' > 바꿀값 . > 날짜로 변경
거래시간은 시간으로, 내용은 변환 > 열분할 > 구분기호기준 > 괄호 기준
지역 > 값바꾸기 닫는 괄호 없애기
머리글만 더블클릭하여 열이름 변경하면 짜잔 끝

7월 데이터 추가 딱 하면 우클릭 새로고침 하면 뾰로롱

7월이 제일 위에 나온다? 편집으로 가서 거래 날짜를 오름차순으로 정렬 하면 됨
(폴더 내 파일 먼저, 그다음 폴더 안을 열기 때문에 그럼)

만들어진 쿼리의 열 너비를 넓히고 새로고침하면 다시 좁아져버릠ㅋㅋㅋㅋ
테이블 디자인 > 속성 > 열 너비 조정 클릭 > 보기 좋게 다듬다듬 하기 >
데이터 막대 추가 > 정렬 이런식으로 하면 굿

폴더의 모든 파일을 다 보는 걸 배웠는데...
매일 데이터가 업데이트 되는데 가장 최근 3개 파일만 취합하고 싶다? >>

쿼리에서 원본 > 생성일 수정일 또는 파일이름 기준 정렬을 해준다. 삽입으로 정렬 추가
가장 최근 파일 보고싶다
홈 > 상위 행 유지 > n개만
조건의 상위 3개 (예. 최근 3개 파일 요런 식으루)

ㅡㅡㅡㅡ

파일 취합할 때 범위를 표로 바꾸어야 하나요? 노노 걍 하면 됨

ㅡㅡㅡ

파일 안에 여러 개의 시트가 있을 때 시트를 취합하는 방법! 데이터 가져오기 > 파일에서 > 폴더에서 > 데이터범위에서

폴더 목록에서 사용자지정 열추가,
Excel.Workbook((content))
똑같이 하자

각 파일의 시트 데이터가 나온다.
콘텐츠는 없애고 엑셀파일 확장하여