[파워쿼리기능소개] 5-2. 변환 - 홈 탭의 변환 기능

이번 포스팅은 홈 탭의 변환 카테고리 안에 있는 기능을 다룹니다.

 - 데이터 형식, 첫 행을 머리글로 사용, 값 바꾸기, 열 분할

예제파일_선별진료소목록.xlsx

0.04MB

1. 파워 쿼리의 데이터 형식에 대해서 

변환 기능의 소개에 앞서 파워 쿼리에서 적용되는 데이터 형식에 대해서 알아봅시다. 주로 사용하게 되는 타입은 Date, 텍스트, Number입니다. 데이터 형식에 따라서 데이터를 변환하는 기능이 다르기 때문에 반드시 알아두어야 합니다. 엑셀 시트에서 텍스트 관련  함수와 날짜/시간 관련 함수가 다른 것과 동일하다 생각하면 되겠네요. 

MS 문서에 소개된 데이터 형식

 

2. 데이터 형식 변환 

현재 필드의 데이터 형식이 무엇인지는 필드 헤더의 아이콘을 보면 알 수 있습니다. 

순서대로 날짜, 텍스트, 숫자입니다. 데이터를 쿼리 해온 처음에는 모든 필드의 데이터 타입이 "ABC,123"로 표시됩니다. '임의'라는 의미입니다. 그리고 불러오자마자 바로 데이터 형식을 각 열의 데이터를 분석하여 자동으로 지정해줍니다. 

원본은 모두 임의로 데이터 형식이 지정되었습니다.

하지만 바로 다음 단계에서 각 필드에 맞는 형식으로 변경을 해줍니다. 

허나 이때 항상 정확하게 우리가 원하는 데이터 형식을 지정해주는 것은 아닙니다. 한 개의 필드에 날짜와 공백이 섞여 있는 경우 텍스트로 구분하는 등의 문제가 발생됩니다. 이럴 경우 해당 공백의 데이터를 'null'로 바꾸어 주게 되면 날짜 형식으로 변환이 가능합니다. 만약 텍스트를 날짜 또는 숫자로 변경하게 되면 화면에 해당 값이 'Error'로 표시됩니다. 

텍스트인 주소 필드를 날짜 형식으로 변경하면 Error 로 처리됨. 

물론 숫자를 텍스트로 변환하는 것은 가능합니다. 

변경하고자 하는 필드를 선택하고 변경할 형식을 클릭하면 변경처리가 됩니다. 

3. 값 바꾸기 

값 바꾸기는 엑셀 시트에서 Ctrl+H로 값을 바꾸는 것과 동일합니다. 쿼리 된 데이터를 변경해주는 것이죠. 찾을 값과 매치되는 셀의 데이터를 변경해줍니다. 

'찾을 값'을 입력하고 '바꿀 항목' 입력하여 확인을 누르면 변경이 됩니다. 매우 간단한 기능인데 함수를 적용하여 동적으로 사용할 수 도 있습니다. 데이터 형식에서 빈 값을 'null(값없음)'으로 변경하려면 바꿀 항목에 null을 입력하면 됩니다. 

살펴봐야 할 것은 고급 옵션입니다.

• 전체 셀 내용 일치 : 셀의 일부가 아닌 전체 값이 모두 일치할 경우에만 변경을 수행합니다. 

• 특수문자를 사용하여 바꾸기 : 바꿀 항목에 탭이나 줄 바꿈 처리를 하도록 할 수 있습니다. 

줄바꿈이 적용된 결과

줄 바꿈이나 공백을 넣기보다는 없애는 방식이 더 자주 사용될 텐데요. 반대로 처리하면 됩니다. 찾을 값에 공백, 줄 바꿈을 넣어서 찾아 바꿔주면 됩니다. 

4. 첫 행을 머리글로 사용 

지난 가져오기 편에서 CSV 파일을 쿼리로 가져올 때 자동으로 적용된 기능입니다. 가장 위에 있는 데이터 행을 각 필드의 헤더(머리글)로 승격해 주는 기능입니다.  

5. 열 분할

개념은 엑셀의 텍스트 마법사로 나누기 기능과 동일합니다. 엑셀에서는 '위치와 구분 기호'로 나누는 2가지 기준만 있지만 파워 쿼리는 더 많은 기준이 있습니다. 

• 구분 기호 기준 : 엑셀의 텍스트 마법사와 비슷합니다. 예시 파일의 '대표 전화번호' 필드의 "-"을 구분기호로 값을 나누게 되면 "-" 을 제외 한 나머지 값들로 열이 분할됩니다. 고급옵션으로 시작 지점 등을 지정할 수 있습니다. 
• 문자 수 기준 : 지정된 문자수 기준으로 열을 분할합니다. 
• 위치 별 : 위치를 지정하여 열을 분할 합니다. 
• 소문자 대 대문자 (대문자 대 소문자) : 영문의 대/소 문자가 다른 지점을 기준으로 열을 분할 합니다. 
• 숫자 대 비 숫자(비 숫자 대 숫자) : 숫자와 비 숫자가 다른 지점을 기준으로 열을 분할합니다. 

열 분할을 수행하면 새로 생긴 열들은 분할 전 열을 이름에 숫자를 증가시키면서 헤더 이름이 붙여집니다. 기능 수행 후 자동으로 분할 후 적합한 데이터 형식으로 변환까지 처리됩니다. 

열 분할은 거의 텍스트 형식의 데이터에 적용하게 됩니다. 열 추가 탭의 "추출" 기능과 함께 상당히 많이 사용되는 유용한 기능입니다.