DAX와 파워비아이

파워비아이 보고서 페이지에서 작업 시, 데이터 필터링을 위해 슬라이더를 활용하는 경우가 많습니다. 슬라이서에 데이터가 많지 않으면 모든 리스트가 슬라이서에 표시가 되지만, 과도한 데이터가 포함되어 있다면 슬라이서내에 모든 데이터가 표시되기는 어렵습니다.

이 경우 아래와 같이 오른편에 있는 스크롤바를 활용해 데이터를 찾아야 하는 번거로움이 발생할 수 있습니다.

미국의 주를 데이터로 활용하여 슬라이더를 생성하려고 할 때, 총 50개 이상의 데이터가 있기에 이 또한 슬라이더 내에서 모든 데이터를 동시에 표시할 수 없습니다. 이러한 상황에서 슬라이더에 '검색' 기능을 활성화하면 데이터를 보다 간편하게 검색할 수 있습니다.

검색창 활성화

슬라이더 내 검색 창은 기본적으로 감춰져 있으며 활성화하면 즉시 활용할 수 있습니다. 예를 들어, North Carolina 주를 슬라이더 내에서 검색 후 선택하고자 할 경우, 다음 단계를 따라 검색 창을 활성화하고 검색창을 통해 North Carolina 주를 빠르게 찾을 수 있습니다:

1. 오른쪽 상단에 있는 세 개의 점을 클릭하여 추가 옵션 메뉴로 이동합니다.
2. 이곳에서 최상단에 있는 '검색' 옵션을 선택하면 검색 창이 즉시 표시됩니다.
3. 검색 창에 원하는 데이터를 입력하고 해당 데이터를 선택합니다.

시각적 개체 슬라이더의 숨겨진 기능에 대해 살펴보았습니다. 이러한 유용한 기능들이 여러 곳에 숨어 있으며, 숨겨진 기능을 제대로 활용한다면 작업을 더욱 효율적으로 수행하는 데 도움이 될 것입니다.

파워쿼리에는 간단히 버튼 하나만 클릭하면 복잡한 수식을 입력하는 것과 똑같은 효과를 볼 수 있는 명령이 있는데요.

그 중 대표적인 것이 그룹화입니다.

엑셀을 써보신 분들은 조건에 맞는 합이나 행의 개수를 파악하기 위해 SUMIF, SUMIFS, COUNTIF, COUNTIFS같은 수식을 수시로 썼을 거라 생각합니다.

처음에는 복잡해 보이지만 수식을 이해하면 이처럼 편한 수식이 없죠.

아마 엑셀 수식 중 가장 많이 사용되는 수식중 하나가 아닐까 합니다.

DAX에서 FILTER를 이용해 구할 수 있지만, 만일 쿼리 에디터에서 작업을 해도 된다면 아주 쉽게 해결할 수 있습니다.

그룹화는 무엇인가?

그룹화는 위에 설명해 드린 데로 열의 조건에 따라 원하는 열의 데이터를 연산하는 것입니다. 조건이 몇 개든 요약하고자 하는 연산이 몇 개건 상관없이 한 번의 명령으로 실행되는 것이 장점이며, 아래에 보시는 것과 같이 합계, 평균 등 총 8개의 연산을 제공합니다.

그룹화 사용 방법

먼저 그룹화는 홈 탭을 통해 실행할 수 있으며, 명령 실행 후에는 위에서 보는 것과 같이 새 창이 나옵니다.

기본은 하나의 열을 조건으로 한 하나의 연산을 실행하는 것이고, 고급을 누르면 SUMIFS나 COUNTIFS와 같이 복수의 조건을 만들 수 있습니다.

그리고 집계 추가 버튼도 나오는 데 이를 이용해 추가 연산도 가능하게 됩니다.

만일 그룹화 명령을 실행하기 전에 복수의 열을 선택하면 바로 고급 옵션으로 들어가게 됩니다.

그룹화의 예

만일 아래의 표에서 Brand과 Type을 그룹으로 하여  총 판매량과 평균 리콜 비율을 알고 싶다면 Brand와 Type 두 열을 선택하고 그룹화를 누릅니다. 그다음 새 열 이름을 넣고 연산과 열을 선택하면 됩니다. 여기서는 연산을 합계, 평균 그리고 열은 Year와 Recall을 선택하면 되겠죠.

그룹화의 단점

그룹화를 실행하게 된다면 일단 쿼리내의 테이블이 그룹화된 형태로 바뀌게 됩니다. 이 말은 기존 데이터는 더 이상 볼 수 없다는 것을 의미합니다.

만약 데이터의 최종 형태가 그룹화라면 크게 상관없지만 그렇지 않다면 기존 데이터를 사용할 수 있는 다른 방법을 찾아봐야 합니다.

만약 데이터를 없애지 않고 계속 두고 싶다면

테이블 형태가 그룹화가 최종이 아니라 데이터를 이용해 전처리 작업을 더 진행하고 싶은 경우는 2가지 방법을 고려할 수 있습니다.

• 기존 쿼리를 복사해 데이터를 그대로 두고 그룹화 명령을 실행한 뒤, 다시 기존 테이블로 돌아와  다른 작업을 진행
• DAX에서 FILTER 명령으로 똑같은 효과를 얻을 수 있으므로, 쿼리 에디터를 빠져나와 측정값이나 데이터 보기 창에서 CALCULATE와 FILTER를 이용하여 함수를 만든다.

이상 그룹화에 대해 말씀드렸는데요, 쿼리 에디터 내에서는 복잡한 수식 없이 간단한 방법으로 조건에 따라 연산을 수행할 수 있으니 목적에 맞게 잘 사용하면 좋은 명령이라 생각합니다.

지금까지 반복적으로 추가가 필요한 데이터 관리를 어떻게 하셨나요?

만약 데이터를 하나하나 추가한 뒤 다시 파워비아이를 수정하는 작업을 반복했다면, 폴더로 데이터 가져오기를 하면 아주 간단히 추가되는 데이터가 있어도 간단하게 폴더에 추가시킨 뒤 파워비아이에서 새로고침만 해주면 바로 모든 데이터가 보고서에 적용됩니다.

이는 Binary라는 좀 독특한 데이터 저장 방식이 있어서 가능한데요.

Binary에 저장된 모든 엑셀 데이터를 Excel.Workbook이라는 수식으로 불러와 새로운 열을 만들면 아무리 많은 데이터가 있더라도 한 테이블 안에 다 불러올 수 있습니다.

여기에 열 분할을 이용해 파일 이름을 새로운 연도 열로 만들어 적용시키면 데이터가 추가될때마다 기존 폴더에 파일만 넣어주면 몇 초 만에 새로운 데이터가 적용된 보고서를 얻을 수 있습니다.

파워쿼리 에디터를 통해 아주 간단히 만들 수 있는데, 한번 보시고 업무에 적용해 보세요.

파워 쿼리에서 작업하면, 데이터를 목적에 맞게 나누어야 할 때가 많습니다.

예를 들어 텍스트가 너무 길다거나, 날짜 데이터에서 연도, 월을 따로 구분한다거나, 고객 명단에서 성과 이름을 각각 다른 열에 저장하고 싶을 때 버튼 하나로 아주 간단히 해결할 수 있습니다.

열 분할 이란?

열 안에 있는 문자나 숫자 등을 필요한 만큼 자르거나 나누는 기능입니다.

홈 혹은 변환 탭 안에서 찾을 수 있는데, 열 분할 버튼을 누르면 총 7가지 선택사항이 나오는데, 여기서 가장 많이 사용하는 구분 구호 기준, 문자 수 기준에 대해 말씀드리겠습니다.

구분 구호 기준

Date 열을 보면 연-월-일로 정리가 되어있습니다.

여기서 똑같이 반복되는 기호가 -이라는 건 금방 알 수 있습니다.

여기서 연, 월, 일을 각각 하나의 열로 만들고 싶다면 -를 기준으로 데이터를 정리하면 쉽게 만들 수 있겠죠.

열 분할에서 구분 구호 기준을 선택하면 창이 나오고, 구분 기호 선택 또는 입력란에 파워비아이가 구분 기호를 자동으로 인식해서 열 분할 준비를 합니다. 만일 다른 기호를 이용하고 싶다면 메뉴에서 선택하든지 아니면 자유롭게 입력할 수 있습니다.

여기서는 파워 쿼리가 자동으로 - 를 구분 기호로 인식하고 있네요.

여기서 어떻게 분할할지 선택하면 됩니다

• 맨 왼쪽 구분 기호에서 - 구분 기호에서 맨 왼쪽 하나만 분할
• 맨 오른쪽 구분 기호에서 - 구분 기호에서 맨 오른쪽 하나만 분할
• 각 구분 기호에서 - 모든 구분 기호에서 분할

선택한 뒤 확인을 누르면 아래와 같이 연, 월, 일의 열이 자동으로 생성됩니다.

파워쿼리에서 작업을 하면 명령을 실행할 때마다 자동으로 속성이 지정되는 걸 볼 수 있는데, 여기도 예외는 아니네요.

만일 연, 월, 일 데이터를 문자 속성으로 사용하고 싶다면, 옆의 적용된 창에서 마지막 단계를 없애면 쉽게 해결할 수 있습니다.

문자 수 기준으로 데이터 분할하기

아래의 Month Name이라는 열에서 달 이름이 너무 길어서 Jan, Feb, Mar과 같이 3자로만 표현하고 싶다면 이 기능을 이용하면 됩니다.

문자 수 기준을 선택한 뒤 창이 나오면 원하는 길이를 입력합니다.

• 가능한 한 왼쪽 끝에서 한 번  - 입력한 숫자만큼 왼쪽에서부터 적용
• 가능한 한 오른쪽 끝에서 한 번 - 입력한 숫자만큼 오른쪽부터 적용
• 반복적으로 - 왼쪽부터 입력한 숫자만큼 계속 반복해서 길이를 나눔

아래의 테이블에서 달 이름이 너무 길어서 Jan처럼 3자로 나타내고 싶은 것이니 문자수에 3을 입력하고 가능한 한 왼쪽 끝에서 한 번 을 선택하고 확인을 누르면 다음과 같은 결과를 얻을 수 있습니다.

나머지 명령도 제목으로 쉽게 짐작할 수 있으니, 상황에 맞게 사용하시길 바랍니다.

눈에 보이지 않는 데이터의 공백

데이터 전처리에서 눈에 보이지 않는 요소로 인해서 많은 양의 데이터가 누락되는 경우가 많습니다

아래의 예를 한번 보시면 금방 이해가 될 거로 생각합니다.

네 개의 데이터가 모두 같아 보이지만 실제로 파워 쿼리에서는 4개의 각각 다른 데이터로 인식합니다.

세 번째는 차이가 느껴지죠? Power 앞에 공백 하나를 가지고 있습니다.

이 공백 하나 차이로 세 번째 데이터는 다른 데이터로 인식됩니다.

그럼, 나머지는 뭐가 차이가 있을까요?

첫 번째는 아무 글 앞뒤로 아무 공백이 없으며, 두 번째는 마지막 BI 뒤에 공백 하나, 네 번째는 BI 뒤에 공백 2개를 가지고 있습니다.

이러한 차이로 인해 파워 쿼리에서는 상이 값 4개, 고유 값 4개로 인식합니다.

만일 이런 차이점을 무시하고 데이터 전처리를 진행한다면, 기대했던 결과치를 얻을 수 없게 되는것입니다.

Power BI라는 문자와 연결되어야 할 데이터가 결국은 하나로 다 합산이 되지 않고, 결국에는 4개로 분산되기 때문이죠.

그럼 이런 공백은 어떻게 지울 수 있을까요?

공백 제거 (Trim) 를 활용하자

이를 없애는 간단한 도구가 공백 제거 (Trim)입니다.

실행하는 방법은 2가지가 있는데, 하나는 변환 리본에서 서식을 선택하면 공백 제거가 나오고요, 다른 하나는 머리글에 커서를 놓고 마우스 오른쪽 버튼을 누르면 메뉴가 나오는데, 여기서 변환을 선택하면 공백 제거라는 명령을 볼 수 있습니다.

공백 제거 명령을 실행하면 글자 전후로 들어있는 모든 공백을 없애주고, 열 분포를 다시 확인하면 기존 4종류였던 데이터 개수가 1개로 되었음을 알 수 있습니다.

그럼, 정리 (Clean)는 무엇인가?

정리 (Clean) 명령은 공백 제거 (Trim)처럼 많이 사용되지 않습니다.

데이터 보관을 가장 작은 형태로 만들어둔 기존 데이터를 파워 쿼리로 가져오는 경우는 정리 (Clean) 명령을 사용할 일이 거의 사용할 일이 없기 때문이죠 

하지만, 간혹 엑셀에서 데이터를 가져올 때 비록 데이터가 한 셀에 들어 있지만 아래와 같이 두 줄로 입력된 경우를 종종 발견할 수 있습니다.

이럴 때 정리 명령을 사용하면 데이터를 아주 쉽게 변환할 수 있습니다.

공백 제거 (Trim)은 앞뒤 공백만 제거해 주는 반면 정리 (Clean)은 두 줄로 된 데이터가 한 줄로 정리됨을 확인할 수 있습니다.

데이터를 분석한다는 것은 올바른 데이터를 가지고 있어야 제대로 된 분석이 가능합니다. 데이터 분석을 진행하기 전, 문자의 속성을 가진 열은 약간은 번거로운 과정을 거치는 것이 전처리 과정 중 오류를 최소화하는 것은 물론, 올바른 결과치를 얻을 수 있기에 데이터 분석을 시작하기 전 데이터를 정리하는 습관을 지니는 것이 좋습니다.

열 이름으로 사용될 행을 머리글로 지정했다면 다음을 가장 먼저 확인해야 합니다.

• 열 속성
• 오류 확인

속성은 아래와 같이 크게 5가지로 구분되나 숫자, 날짜, 문자가 가장 많이 사용되며,  숫자, 날짜는 좀 더 세분화 됩니다.

• 숫자
• 날짜
• 문자
• 참/거짓
• 이진법

열 속성 지정

처음 데이터를 가져오면 모든 열 속성이 문자와 숫자가 동시에 보이는데 이는 열 속성 지정이 되지 않았음을 의미합니다.

열 속성을 나타내는 곳을 클릭하면 메뉴가 나오는데 이 중 하나를 선택하면 속성이 바뀌는 것을 확인할 수 있습니다.

주의할 점

열 속성이 지정되지 않아도 작업 진행에는 문제가 없습니다. 하지만, 첫 번째 Segmant 열을 예로 들면 화면상에서는 모두 문자를 보여주고 있지만, 아래 보이지 않는 곳에 수많은 행이 있는데 그 중 숫자나 날짜 같은 것이 섞여 있으면 데이터 전처리 과정에서 오류가 나올 수 있으므로 번거롭겠지만 처음부터 속성을 지정하고 시작하는 습관을 들이는 것이 좋습니다.

오류 확인

아래와 같이 숫자로 이루어진 열에 실수로 문자가 들어갔을 경우 속성을 숫자로 바꾸면 오류가 있다는 것을 한눈에 알기 쉽게 표시하며, 문자가 들어간 행에 에러가 있다고 보여줍니다.

데이터양이 작다면 쉽게 파악이 되나 수많은 데이터를 다루게 된다면, 아주 유용하게 사용됩니다.

이런 경우는 원본 데이터를 수정하거나, 아니면 에러가 있는 행을 삭제하고 데이터 전처리를 진행하면 문제가 없습니다.

열 이름 바꾸기기

혹시 열 이름을 임의대로 바꾸고 싶다면 열 이름을 더블클릭하고 수정하면 됩니다.

Segment라는 열 이름을 Category라고 바꾸고 싶다면 Segment를 더블클릭한 뒤 Category라고 입력하면 열 이름이 바뀌고, 지금껏 작업한 곳에 열 이름이 자동으로 수정되기 때문에 데이터 변환 중 바뀌더라도 큰 문제가 없습니다.

숫자열과 문자열의 구분

한가지 기억해두시면 좋은 것은 문자열과 숫자열의 구분인데 간단한 구분법은 아래와 같습니다.

• 문자열: 좌측정렬
• 숫자열: 우측정렬

지금 Sale Price는 숫자의 속성을 가지고 있으므로 우측정렬이 되어있지만, 속성을 문자로 바꾼다면 아래와 같이 좌측정렬을 하게 됩니다.

열 이름에 있는 속성을 하나하나 확인하지 않더라도 어떤 속성을 가지는지 쉽게 알아볼 수 있습니다.