파워쿼리 기초, 4주 완성 챌린지 [4주차]

🔥 실습 예제과 함께 공부하는 모습을 함께 올려보세요!
(마우스 드래그 & 스크린샷+붙여넣기로 편리하게 그림을 넣을 수 있습니다)

미션 목표

1) 크롬 개발자도구로 웹페이지의 네트워크 동작을 분석한 후, 파워쿼리를 사용해 웹 데이터를 실시간으로 가져오는 방법을 알아봅니다.

개발자도구(F12) - 네트워크에서 파일 종류로 Fetch/XHR 선택 -> 시세 정보가 담겨있는 파일 찾기 (네이버, 삼성전자 시세 차트 : https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20230216&endTime=20231111&timeframe=day)

(?는 이 다음부터는 쿼리(전달해줘야하는 값)가 이어진다는 걸 알려주고 &는 그 값이 여러 개 일 때 붙여 씀.) 여기서 &으로 연결된 symbol, requestType, startTime, endTime, timeFrame 값을 변경하면 원하는 데이터를 불러올 수 있을 것.

-> 위와 같이 정리해준다. 종목 번호와 날짜는 셀에 입력된 값과 들어가는 값이 다르므로 규격에 맞게 변형시켜야함. TEXT 함수를 이용, 해당 셀을 클릭하고 원하는 형태로 정함 [ =TEXT(B3,"YYYYMMDD") ]. 단위는 들어갈 수 있는 값만 정해주기 위해 [데이터 유효성 검사] - [목록] - [원본]에 day, week, month를 적음.

-> 값 변경에 따라 주소 또한 바꿔주기 위해 해당하는 셀을 주소에 붙이기. 전체 주소값을 ""로 감싸준 다음 셀 값이 들어올 때마다 주소부분을 "로 닫아주고 &로 이어준다. ex) "1234"&B2&"5678"

-> 먼저 예시로 찾아놓은 주소를 기준으로 [데이터] - [웹]을 통해 파워쿼리로 만듬. 이 경우 오류가 날 수 있는데, Json이 아닌 파일을 Json으로 불러왔기 때문. 오류 팝업에서 [편집]을 클릭하고 파일 열기 형식을 텍스트 파일로 바꿔주고 확인을 누른 다음 제대로 뜨는 걸 확인하고 데이터로 변환.

-> 해당쿼리는 중간 중간 빈칸이 들어가 있으므로 이를 정리해줘야 한다. [열 추가] - [열 추출] - [길이]로  데이터들의 길이를 알려주는 필드를 생성하고 [숫자필터] - [보다 큼]에 대강 10정도 입력하여 필터링함.

-> [값 바꾸기]를 통해 필요 없는 기호들, 빈칸 정리 -> , 기호를 기준으로 [열 분할]하기

-> 첫 행을 머리글로 만들고 데이터 형식을 바꿈.

-> 데이터가 들어갈 쿼리를 만들었으면 이제 셀 변경 시 주소값도 변경되는 동격주소를 알아내야함. URL이 들어있는 셀을 표로 만들고 이를 파워쿼리로 만든다.

-> 우리가 사용하고자 하는 데이터는 URL 필드의 첫번째 값임. 따라서, " = Excel.CurrentWorkbook(){[Name="tblURL"]}[Content][URL]{0} "으로 해당 값을 찾는다.

-> 데이터 쿼리에서 고급 편집기로 들어간 다음 해당 값을 변수로 지정해 원래 주소값이 있던 곳에 넣음.

-> 그러면 셀 값에 따라 데이터를 불러올 수 있음.

(데이터 변동에 따라 너비가 변경되는 케이스를 막기 위해 [테이블 디자인] - [속성] - [열너비조정]을 체크 해제해주는 것 잊지 않기)

2) 열 피벗해제 기능을 사용해 여러 개의 머리글 쌍으로 구성된 데이터를 정규화하는 방법을 알아봅니다.

(1) 기존 방식

해당 데이터를 표로 바꾸고 (머리글 포함 해제) 파워쿼리로 만든다 -> 빈 값들을 [채우기] - [아래로]를 이용해 채움. -> 행 열을 바꾸고 똑같은 방식으로 빈 값을 채움 -> 직원정보, 부서를 선택하고 [열 병합] -> 다시 행 열을 바꾼다

-> 첫 행을 머리글로 바꾸고 TOEIC,OPic... 등등의 데이터를 구분~취득번호까지 묶어 하나로 병합 (열병합 시 데이터에 들어가지 않은 기호를 기준으로 할 것)

-> 각각의 구분 기호에 따라 [열 분할]하기 -> 필터기능으로 빈 값을 필터링 -> 데이터 정리 후 끝

이 경우 발생하는 문제 : 머리글이 변경 되었을 때 오류가 남. 따라서 정규화가 필요

(2) 정규화

우클릭 - [새 쿼리] - [기타 원본] - [빈 쿼리]를 통해 위의 함수를 쿼리로 만들어 준다. -> 데이터에서 머리글 변경으로 인해 오류 난 부분 전으로 이동하여 그 아래 단계를 다 지워 줌. 우클릭 - [끝까지 삭제]

-> [열 추가] - [사용자 지정 열]에서 해당 수식을 입력. {} 안에 공통으로 들어가는 항목을 넣어주면 된다.

-> 불필요한 파일을 제거 시 수식에 머리글이 포함되지 않도록 제외한 열을 선택 후 [다른 열 제거]

-> 생성된 사용자 지정 열을 확장 후 정리해주면 끝

3)  '쿼리 병합(JOIN)'의 원리를 이해하고, 쿼리의 기본 연결부터 다중조건 VLOOKUP을 자동화하는 방법까지 단계별로 알아봅니다.

자신이 사용하고 있는 테이블을 기준으로 다른 테이블에 있는 값을 병합함.

- 가장 많이 사용하는 쿼리 병합 방법

(1) 왼쪽 외부 - 기준으로 하는 테이블에 있던 데이터를 유지하고 일치하는 것을 불러온다

(2) 완전 내부 - A, B에 공통으로 있는 것만 불러옴

(3) 완전 외부 - 있든 없든 다 불러옴 (없는 데이터는 null처리 하고 한 테이블에 묶음)

- 다중조건 VLOOKUP 자동화

(1) 병합 열 선택 시 해당되는 열 다 선택

--> 머리글 변경 시 오류 발생 가능

(2) 해당 조건을 병합 해 임시필드로 만들기

해당 열 선택 후 [열 추가] - [열 병합] -> 생성된 임시필드를 기준으로 쿼리 병합하기.

스스로 점검하기

1) 이번 파워쿼리 4주 챌린지를 통해 배운 내용 중, 가장 인상 깊었던 내용 또는 기능은 무엇인가요? 가장 인상 깊었던 기능 3가지를 간단하게 정리합니다.

(1) 데이터 취합하기 : 여러 파일로 나누어져있는 데이터를 취합하여 하나의 쿼리로 만드는 과정이 생각했던 거보다 복잡하지 않고 순식간에 해결돼서 신기했음.

(2) 동적 경로 받아오기 : 프론트엔드 쪽 코딩을 했었던 터라 변수 받아와 집어넣는 과정은 익숙했고, 엑셀은 기본만 알았는데 알고보니 비슷한 경과로 처리하더라. 엑셀 함수 쪽에 관심이 생긴 계기가 되었음.

(3) 웹에서 전달 받은 데이터를 불러와 파워쿼리로 만들기 : 위와 같은 이유로 웹에서 데이터를 받아오는 건 잘 알고 있었는데 이를 엑셀로 변환해 원하는 데이터로 받아오는 과정 자체가 새로웠음.

2) 이번 4주 챌린지를 진행하면서, 추가로 배우고 싶은 내용과 좋았던 점, 아쉬웠던 점을 자유롭게 남겨주세요! 다음 챌린지를 진행할 때 개선하도록 하겠습니다.

추가로 배우고 싶었던 내용은 앞에서 말했다시피 함수. 중간중간 알려주신 함수를 붙여넣기하여 과정을 넘어갔지만, 그게 어떻게 동작하는지 자세히 알 수가 없어서 아쉬웠어요. 주어진 시간이 부족했던 것 때문이라 생각하여 따로 추가 공부할 예정입니다.

좋았던 점은 엑셀에 대한 상식이 기초인 사람도 이해할 수 있도록 상세하게 설명해주신 것.

원래 어떠한 것을 꾸준히 하는 것을 잘 하지 못하는 사람이어서 이번 4주 챌린지를 제대로 끝낼 수 있을까, 중간에 숙제를 까먹고 올리지 못하는 불상사가 일어나지 않을까 걱정을 많이 했는데요, 매번 강의 준비를 열심히 하시고 이해가 될 때까지 이끌어주시는 모습을 보고 이에 보답(?)하고자 하는 마음으로 있는 끈기 없는 끈기 다 끌어모았더니 끝까지 완주할 수 있었네요. 저에겐 이번 경험이 어떠한 터닝포인트가 될지도 모르겠어요.

이번 챌린지로 유입되었는데 앞으로의 라이브도 꼭 챙겨보도록 하겠습니다. 4주동안 정말 감사했습니다.