엑셀 파워 쿼리 데이터 가져오기(웹, API 사용)

저번 시간에는 API를 사용하지 않고 웹 데이터를 가져오는 방법에 대해 알아보았다. 이번 시간에는 API를 사용하여 데이터를 파워 쿼리 편집기로 가져오는 방법에 대해 알아보려고 한다. 그전에 API가 어떤 것인지 알고 싶다면 아래 포스팅을 참고하도록 하자.

인터페이스와 API

목차
1. API를 제공 사이트 찾기
2. 데이터 가져오기

---

1. API 제공 사이트 찾기

보통 공공기관의 정보의 경우 API를 통해 쉽게 접근이 가능하다. 사용 가능한 오픈 API에 대해 알고 싶다면 아래 웹사이트에 접속하여 목록을 조회해 보자. 공공데이터 포털은 공공의 목적으로 개방된 데이터들에는 어떤 것들이 있는지 알려주는 사이트다.

공공데이터 포털

이번 포스팅에서는 기상청 단기예보 정보를 오픈 API를 이용해 엑셀로 가져와보겠다. 원하는 데이터를 선택하고 활용신청을 한다. 참고로 인증키에 대한 승인은 바로 자동승인이 되거나 약간의 시간이 흐른 뒤에 승인이 난다.

API를 사용하기 위해서는 인증키가 필요하다. 인증키를 보기 위해서 공공데이터포털 사이트의 우측 상단에 있는 마이페이지를 선택한다.

마이페이지에 접속하면 오픈 API 신청 목록을 확인할 수 있다. 오픈 API를 사용하기 위해서는 인증키가 필요하다. 인증키를 확인하기 위해 제목을 클릭한다.

그리고 나면 위와 같이 서비스 정보가 나타난다. 일반 인증키(Encoding)에 쓰여있는 텍스트를 복사하여 엑셀의 웹 URL 입력 창에 입력해야 한다. 참고로 API 인증키를 배포하는 것은 문제의 소지가 있으므로 노출되지 않도록 주의한다.

참고로 "SERVICE KEY IS NOT REGISTERED ERROR." 에러메시지는 인증키가 서버에 등록되기 이전에 해당 인증키를 사용하려고 했을 때 나타나는 메시지다. 서버에 해당 인증키가 등록될 때까지 시간이 소요되므로 기다려야 한다. 또는 인코딩 문제로 해당 에러메시지가 표출될 수도 있다고 한다.

서비스 정보 아래에 '활용신청 상세기능정보'가 있다. 나는 초단기실황조회 기능을 사용하여 특정 지역의 기상 실황정보를 알고 싶다. 초단기실황조회 행의 우측에 있는 확인 버튼을 클릭한다.

그리고 나면 데이터를 얻기 위해 입력해야 할 변수들에는 어떤 것들이 있는지 나타난다. 하지만 아직 우리는 각각의 변수들에 어떤 값을 입력해야 할지 모르는 상황이다.

해당 변수들에 대한 설명은 보통 서비스 정보란에 있는 첨부파일에서 확인할 수 있다. 첨부파일을 다운로드한다.

첨부파일은 압축파일인데 압축을 해제하면 위와 같이 2개의 파일이 있다. 엑셀 파일에는 지역별 X, Y 좌표가 입력되어 있다. 각각의 변수에 대한 설명을 읽고 싶으므로 Word 파일을 실행한다.

본문을 살펴보면 상세기능을 활용하기 위해 입력해야 하는 변수들에 대한 설명이 적혀있다. 이 문서의 내용을 참고하여 변수를 입력해 보자.

예보지점의 X, Y 좌표를 입력할 때는 엑셀 파일의 내용을 참고한다.

다시 공공데이터포털 사이트로 돌아가서 샘플데이터란에 각각의 변수를 입력해야 한다. 참고로 ServiceKey에는 서비스 정보란에 적혀있는 일반 인증키(Encoding)를 입력한다. 모두 입력이 되었다면 미리 보기 버튼을 누른다.

그리고 나면 에러메시지가 출력되지 않고 데이터가 정상적으로 나오는 것을 볼 수 있다. 이렇다면 성공이다. 이제 해당 미리 보기 사이트의 URL을 복사한다.

---

2. 데이터 가져오기

데이터를 가져오기 위해 엑셀을 실행시킨다. 그리고 상단 메뉴에서 '데이터'를 클릭한다. 그리고 바로 '웹' 버튼을 클릭하거나 데이터 가져오기 → 기타 원본에서(O) → 웹(W) 순으로 클릭한다.

이제 데이터를 가지고 올 사이트의 URL을 입력하라는 창이 나타난다. 방금 복사해 두었던 URL을 입력한다. 그리고 확인을 누른다.

이제 탐색 창이 켜진다. 해당 URL을 통해 가지고 온 데이터들은 body 테이블에 들어가 있다. 따라서 좌측 목록에서 body를 선택하고 데이터 변환을 누른다. 참고로 header 테이블을 눌러보면 resultCode는 "0"이고 resultMsg에는 "NORMAL_SERVICE"가 입력되어 있다. 이는 데이터를 정상적으로 불러들여왔음을 뜻한다.

header 테이블에 나타나는 값들에 대한 설명 또한 공공 데이터 포털에서 다운로드했던 API 가이드 Word 파일에 적혀있다. 문제가 발생했을 경우 해당 파일의 내용을 참고하면 원인이 무엇인지 알 수 있다.

데이터 변환을 클릭하고 나면 파워 쿼리 편집기가 실행된다. 그리고 body 테이블의 데이터가 입력되어 있다. 여기서 'items' 열에 있는 'Table'이라는 글자를 클릭해 준다.

그러고 나서 한 번 더 'Table'이라는 글자를 클릭해 준다.

그리고 나면 마침내 원하는 데이터가 나타난 것을 확인할 수 있다. 하지만 우리는 아직 각각의 열이 무엇을 의미하는지 모르는 상황이다. 이를 위해서 다시 Word 파일(API 가이드)을 연다.

본문을 살펴보면 각각의 코드가 무엇을 의미하는지 적혀있다. 이 코드표를 참고하여 파워 쿼리 편집기에 적혀있는 데이터를 해석할 수 있다.

• 강수형태 : 0(없음)
• 습도 : 79%
• 1시간 강수량 : 0
• 기온 : -6.2℃
• 동서바람성분 : -0.2m/s
• 풍향 : 44(N-NE)
• 남북바람성분 : -0.3m/s
• 풍속 : 0.5m/s