텍스트 파일의 차원 테이블: 파워 쿼리 솔루션

에서 진행 중인 이번 주 문제의 2번 문제에 대해 살펴보겠습니다 . 여기서 지저분한 데이터를 깨끗한 차원 테이블로 정렬하는 방법에 대해 논의했습니다. 이것은 LuckyTemplates에서 주최하는 이 새로운 이니셔티브의 두 번째 반복입니다. 모든 사람에게 정기적으로 더 많은 연습을 할 수 있는 기회를 제공하기 때문에 저는 특히 이 시리즈에 열광합니다. 이 블로그 하단에서 이 튜토리얼의 전체 비디오를 볼 수 있습니다.

매월 첫 번째 수요일에는 DAX 챌린지가 있고 세 번째 수요일에는 파워 쿼리 챌린지가 있습니다 .

이것은 LuckyTemplates 내에서 활용해야 하는 이러한 언어에 대한 새로운 것을 탐색하고 발견하고 배울 수 있는 좋은 기회입니다.

LuckyTemplates 포럼에서 금주의 문제 카테고리를 찾을 수 있습니다.

주요 초점은 결과가 아니라 과정에 관한 것입니다.

문제를 작은 조각으로 나누는 데 성공하면 직면한 모든 문제를 해결할 수 있습니다.

멜리사 데 코르테

Brian은 이전에 고무 오리 디버깅 이라는 기술에 대해 이야기했습니다 . 놓치셨다면 그의 영상을 꼭 확인하세요. 이러한 문제에 얽매일 때 도움이 될 수 있습니다.

먼저 당면한 작업을 살펴보겠습니다. 적절한 차원 테이블로 변환해야 하는 지저분한 텍스트 파일이 있습니다.

이제 이와 같은 데이터를 보면 대부분 텍스트 정리, 원치 않는 문자 제거, 자르기, 단어 대문자 사용 등에 관한 것입니다.

하지만 이 스택 데이터 에서 모든 행을 검색하여 각 국가에 대해 한 줄로 변환해야 합니다. 모든 필드 이름이 각 국가의 단일 열에서 반복되기 때문에 스택 데이터라고 합니다.

저는 Excel에 대한 경험이 있으며 제가 보기에 Excel의 가장 강력한 기능 중 하나는 피벗 테이블 입니다 .

목차

• Excel의 피벗 테이블
• 파워 쿼리를 사용하여 차원 테이블 만들기
• 사용자 지정 텍스트 정리 기능
• 데이터 피벗
• 결론

Excel의 피벗 테이블

피벗 테이블을 사용하면 세그먼트별로 데이터를 볼 수 있습니다. 행 섹션에 배치하는 항목에 따라 피벗 테이블은 해당 세그먼트의 각 항목을 한 줄로 압축합니다.

그런 다음 필드를 열 섹션으로 드래그하여 더 분할할 수 있습니다.

이것이 어떻게 생겼는지에 대한 예를 만들었습니다. 여기에는 텍스트 파일에 있는 것과 거의 동일한 데이터가 있습니다.

그리고 다음 페이지에서 빈 피벗 테이블을 만들었습니다.

자, 제가 보여드리지 않은 것은 제가 이미 보여드린 두 개의 기둥 대신 여기에 세 개의 기둥이 있다는 것입니다.

내 세그먼트를 행으로 드래그하겠습니다.

열 1에는 필드 이름이 있으므로 열 섹션으로 드래그하겠습니다.

열 2에는 모든 필드 값이 있으므로 이를 값 섹션으로 드래그하겠습니다.

여기에서 피벗 테이블이 텍스트 문자열을 처리할 수 없음을 알 수 있습니다. 개수를 세지만 각 필드에 대해 단일 값이 있음을 보여줍니다.

이제 내가 만든 세그먼트를 살펴보겠습니다. 데이터로 돌아가 열 숨기기를 해제하겠습니다. 여전히 서로 위에 쌓인 개별 데이터 블록 각각을 식별하는 색인 ​​번호임을 알 수 있습니다.

따라서 저에게 중요한 변환은 데이터를 다시 표 형식으로 가져오는 중심축이 될 것입니다.

포럼에서 나는 이것을 다루는 다른 방법을 보았습니다. 피벗 작업을 사용하는 것 외에 테이블 형식을 얻는 다른 방법이 있으며 모두 동일하게 잘 작동합니다. 관심이 있는 경우 포럼으로 이동하여 탐색을 시작하십시오.

파워 쿼리를 사용하여 차원 테이블 만들기

파워 쿼리를 살펴보고 내 솔루션을 검토해 보겠습니다.

개인적으로 저는 UI가 우리를 위해 대량의 M 코드를 작성하는 데 큰 역할을 한다고 생각합니다. 그래서 저는 최대한 사용자 인터페이스를 사용하여 쿼리를 디자인하는 경향이 있습니다.

쿼리가 필요한 작업을 수행하면 고급 편집기로 이동하여 M 코드를 검사하여 수정할 수 있는지 확인합니다. 어떻게 생겼는지 봅시다.

이것은 사용자 인터페이스를 사용하여 구축된 내 기본 그룹입니다.

오른쪽의 적용된 단계에 많은 단계가 있음을 알 수 있습니다.

지금은 그 자체로는 문제가 없지만, 이 단계만 봐도 그룹화할 수 있는 변환이 많다는 것을 알 수 있습니다.

고급 편집기를 열어 봅시다.

이 쿼리에는 31단계가 있습니다.

또한 31단계를 포함하는 동일한 쿼리에 몇 가지 설명을 추가했지만 섹션으로 나누었습니다.

가장 먼저 한 일은 유형 변경 단계를 제거하는 것이었습니다. 이러한 모든 텍스트 변환 단계를 수행하는 사용자 지정 함수를 만드는 것이 좋습니다.

여기에 많은 댓글이 있지만 이 블로그 게시물에서 강조하고 싶은 것은 두 가지뿐입니다. 첫 번째는 텍스트 정리를 위한 사용자 지정 기능 입니다 .

두 번째는 이러한 유형의 데이터를 적절한 표 형식으로 되돌리는 피벗 단계입니다 .

사용자 지정 텍스트 정리 기능

쿼리를 작성하는 초기 단계로 돌아가서 이러한 텍스트(열 1 및 병합된 열)를 정리하기 위해 모든 그룹화된 단계가 있었습니다.

또한 추가 사용자 지정 열을 추가했습니다. 그것의 유일한 목적은 내 사용자 지정 텍스트 청소 기능을 구축하는 것입니다 . 병합된 열에서 호출했습니다.

이렇게 하면 한 번에 함수를 작성할 필요가 없고 이전 단계의 결과를 검토한 후 새로운 변환을 추가하면서 한 번에 한 단계씩 점진적으로 빌드합니다.

문자 정리 기능을 위한 M 코드를 살펴보겠습니다.

보시다시피 여러 단계가 있습니다. 이 텍스트 함수를 구축할 때 쿼리 사이를 오가며 결과를 보고 무엇을 구축하고 다음에 무엇을 수정할지 확인했습니다.

이 결과로 필요한 모든 변환을 수행했습니다. 여기에서 사용한 M 함수 중 일부는 텍스트와 같은 초기 쿼리를 작성할 때 사용자 인터페이스에서 제공되었습니다. 트림 기능. 그러나 사용된 다른 기능은 그렇지 않았습니다.

익숙하지 않은 경우 온라인 M 공식 가이드에서 모든 M 함수를 찾아볼 수 있습니다. 이것은 당신이 가야 할 링크 입니다.

파워 쿼리 및 기능 전용 섹션을 찾을 수 있습니다.

아래로 스크롤하면 텍스트 기능에 대한 섹션이 있으며 각 섹션은 개요로 시작합니다. 파워 쿼리 및 수식 M 언어 내부의 모든 텍스트 함수 목록이 있습니다.

특정 변환을 찾고 있다면 바로 여기에서 찾아볼 수 있습니다.

데이터 피벗

이 차원 테이블을 만들면서 강조하고 싶었던 두 번째 부분은 데이터 자체의 피벗입니다. 이것도 자세히 살펴보겠습니다.

색인을 추가하여 시작했습니다. 데이터 블록을 적절하게 분할하기 위해 해당 인덱스를 업데이트했습니다. 열 1에 국가라는 텍스트가 있는 각 줄의 색인 번호를 반환하여 이 작업을 수행한 다음 해당 값을 채웠습니다.

데이터 자체를 피벗하기만 하면 됩니다. 변환 탭에서 피벗 열을 찾을 수 있습니다. 열 1을 선택한 상태에서 피벗 열을 클릭합니다.

열 1의 값을 새 열 이름으로 사용합니다. 또한 해당 필드 이름의 값이 어디에 있는지 알고 싶어합니다. 병합된 열에 있습니다.

Excel에서 텍스트 값을 처리할 수 있는 경우 고급 옵션 설정으로 인해 파워 쿼리도 처리할 수 있습니다. 텍스트 값을 처리할 수 있도록 Don't Aggregate를 선택하기만 하면 됩니다 .

확인을 클릭하면 데이터가 피벗되었음을 알 수 있습니다.

결론

위의 이미지는 최종 결과 쿼리입니다. 제공된 텍스트 파일의 지저분한 데이터를 재구성하여 분석에 적합한 깨끗한 차원 테이블 로 변환한 방법이 마음에 드셨기를 바랍니다 .

이 블로그 게시물이 마음에 드셨다면 새로운 콘텐츠를 놓치지 않도록 LuckyTemplates 채널을 구독해 주세요.

앞으로 금주의 문제 챌린지에서 여러분 모두를 뵙기를 바랍니다.

여자 이름