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

안녕하세요.

업무에서 담당자가 머릿글이 2개인 데이터시트를 줘서, 강의를 듣기전에는 하나하나

머릿글을 분해해서 나눠서 분류했었는데요.

파워쿼리를 배워서 "열피벗 해제"를 하니, 제가 원하는 데이터 분류가 바로 되서 업무 시간을 확실히 단축시킬수 있었습니다.

감사합니다.

**복습

아래의 표의 경우, 머릿글이 2개인 데이터로, 수량과 금액의 서식이 잘못되어있고 상세내역에 내용이 2개 들어가있는 잘못된 함수이다.

이런 데이터구조를 "파워쿼리"를 통해서 수정할 수 있다.

**파워쿼리를 하기전에 해야 하는 것.

먼저, "표"를 만들어야 한다. 표를 만들면, 데이터의 범위가 자동으로 확장되기 때문에 데이터가 자동으로 확장이 되며, 

표를 기반으로 파워쿼리가 작성되기 때문에, 데이터가 추가되면, 자동으로 파워쿼리의 데이터도 Update 된다. 

1) 파워쿼리를 실행 후에 잘못된 데이터를 수정한다.

2)머릿글이 될 행을 만들고, 첫행을 머릿글로 만들기를 한다.

3)잘못된 서식을 바꿔준다.

위의 표는 피벗테이블을 사용했을 때, 보기편한 표이다.

● 머릿글이 3개인 경우, 행/열을 바꾼 후 셀병합 그리고 다시 행/열을 바꿔서 머릿글을 하나로 만들어 준다.
피벗테이블의 기준이 되는 데이터로 열 피벗을 해제하고, 수량/금액도 나누고 싶은 경우에는 열을 피벗화 (값으로)하면 구분이 된다.

■ CSV 파일을 가져오는 방법
엑셀로 불러 왔을때 깨지는 문제가 있다.
타임라인을 적을 때, 각 섹션별로 불러와서.. 넣어주는데..
CSV파일을 받아왔을때 깨질 경우
① 텍스트파일로 가지고 와서 ANSI로 불러오면, 엑셀에서 깨지지않고 볼수있다.
② 엑셀을 키고 엑셀에서 텍스트에서 가져오기를 한다. 🡺 데이터변환을 클릭.

– 교대로 행지우기로 반복되는 행을 제거할 수 있다.
– 시작 시간에서 뒤에있는걸 지우고 싶을 때, 변환 - 추출로 변경할 수 있다.

① 외부에서 불러온 파일을 원본을 변경하고 싶을 때, 원본의 경로를 바꿔주면 된다.
② 동적으로 바꿀 수도 있다. (추후 배울 예정)

■ PDF 에서 표 파일을 가져올 수 있다.
– PDF에서 기본적으로 표를 가져오면, 한줄에 저장되거나, 표가 제대로 가져오기 어렵다.
– 파워쿼리를 통해서 표를 제대로 가져올 수 있다.
※ PDF에서 불러오기 기능은 M365에서만 가능하다.

■ PDF에서 표를 합칠 때, 머릿글이 다를 경우,
□ 열추가에서 사용자 지정열 추가를 한다
열 이름 : Headerdata
=Tabel .Promoteheader([Data])
머릿글 기준으로 Data를 병합할 수 있다.

■ 필터에서 포함하지 않음으로 Data를 제거할 수 있다.
■ 폴더안에 있는 파일을 취합할 경우.
Ⅰ)1,2,3,4월 거래내역 …....
① 데이터가져오기 - 폴더에서 가져오기를 클릭
② 폴더 안에 파일 목록이 나온다. ※ 결합 기능이 있음. // 실무에서는 보통 '데이터변환'을 눌러서, 파일을 정리한다.
③ '파일병합'을 눌러서, '첫번째 파일'을 클릭. = '샘플파일' 이 된다.

■ 파일을 병합하는 과정
◆ 도우미 쿼리 : 매개변수, 샘플파일, 파일변환
◇ 도우미 쿼리와 취합쿼리

■ 2016 EXCEL에서 파일을 취합하는 방법.
① 열추가 : Excel Date
=Excel.Workbook([content])
🡺 Table 이 만들어진다.
column이 임시머릿글이 됨.
🡺 열추가 해서 머릿글을 만든다 : HeaderData
=Table.PromoteHeader([Data])

■ 폴더 안에서, 특정파일만 취합하는 방법.
– 확장자에서 엑셀파일만 보고, 결합하면 된다. ※ 정부나 공공기관의 경우, .Xls, Xlsm등을 나올경우가 있다.
– 텍스프필터에서 xls를 포함하는 걸로 설정.

■ 파일 안에 시트가 여러 개 있을 경우????????? 한번에 취합할 수 있다.
– 파일안에 있는 폴더를 클릭한다.
– Table 안에 머릿글을 만들어 준다.

■ 현재 쓰고 있는 파일에서 다른시트의 데이터를 취합하는 방법.
실행중인 파일에 파일을 취합하는 방법?
① 표로 취합하는 방법
② 범위로 합치는 방법

여려개의 표 이름이 있음.
데이터 가져오기 - 빈쿼리
=Excel.CurrentWorkbook()
■ 현재 시트에 있는 데이터를 가져올 수 있다.
※ 동일한 데이터가… 중복됨 🡺 중복취합 됨.
이름 중에서 취합보고서를 "포함하지 않음"으로 해야 함.