웹사이트 검색

이 숨겨진 엑셀 도구는 데이터를 자동으로 빠르게 정리할 수 있습니다

M은 Power Query를 백그라운드에서 실행하는 강력한 수식 언어입니다. 복잡한 코드처럼 보일 수 있지만, M의 많은 일반적인 작업은 놀라울 정도로 초보자 친화적이며 데이터 정리를 빠르게 자동화할 수 있습니다. 기본적인 엑셀 함수를 작성할 수 있다면, 가장 큰 데이터셋을 처리하기 위해 M 학습을 시작할 수 있습니다.

많은 사람들이 리본의 버튼을 사용하여 파워 쿼리 편집기를 시작하며, 그것 역시 괜찮습니다. 하지만 이 방법은 긴 클릭 마라톤으로 이어지게 만듭니다. M은 여러 변환을 관리, 재사용 및 유지 관리하기 더 쉬운 하나의 단계로 결합할 수 있게 함으로써 혼란을 줄이는 데 도움이 됩니다.

M 언어: 시작하는 방법

Power Query 인터페이스에 익숙해지세요

위 데이터를 방금 받았다고 상상해 보세요. 혼란스러운 주문 ID, 일관되지 않은 대문자 이름, 불완전한 지역 정보가 뒤섞여 있습니다.

이 가이드를 따라가면서 읽으려면, 예제에 사용된 Excel 통합 문서의 무료 사본을 다운로드하세요. 링크를 클릭한 후 화면 오른쪽 상단에서 다운로드 버튼을 찾을 수 있습니다.

M을 사용하여 이를 수정하려면 먼저 환경을 준비해야 합니다. 데이터셋이 이미 T_SalesData라는 Excel 테이블 형식으로 되어 있다고 가정하면, 다음 단계를 따르세요:

  1. 표의 임의의 셀을 선택하고, 데이터 탭에서 표/범위에서를 클릭하세요.
  2. 한 번 Power Query 편집기가 열리면, 데이터 그리드 위의 흰색 막대(수식 입력줄)가 보이는지 확인하십시오. 보이지 않는 경우, 보기 탭을 열고 수식 입력줄을 확인하십시오.
  3. 수식 입력줄 왼쪽에 있는 fx 버튼을 찾으세요. 이 아이콘을 클릭하면 코드를 입력하거나 붙여넣을 수 있는 사용자 지정 단계를 만들 수 있습니다.

스니펫 1: 엉망인 이름 대소문자 표준화

Text.Proper을 사용하여 대문자를 제자리에서 수정하기

목표: CustomerName 열에 있는 이름 중 모든 글자가 대문자이거나, 모두 소문자이거나, 무작위로 섞여 있는 이름을 각 단어의 첫 글자만 대문자로 고치는 것.

새 단계를 만들려면 수식 입력줄 왼쪽에 있는 fx 아이콘을 클릭하세요. 그런 다음, 수식 입력줄의 모든 내용을 다음 코드로 바꾸고 Enter를 누르세요:

= Table.TransformColumns(#"Changed Type", {{"CustomerName", each Text.Proper(_), type text}})

Power Query는 단계에 Custom1과 같은 일반 이름을 지정합니다. Applied Steps 창에서 이 일반 단계 이름을 마우스 오른쪽 버튼으로 클릭하고 이름 바꾸기를 클릭하여 FixCasing과 같이 더 설명적인 이름을 부여합니다.

이 첫 번째 스니펫에 대해서는 아래 표에서 전체 구조를 자세히 분석하겠습니다. 나머지 세 개의 스니펫에 대해서는 전체 패턴이 동일하기 때문에 새롭게 추가되거나 변경된 부분만 집중적으로 살펴보겠습니다.

요소

설명

Table.TransformColumns(...)

기존 열을 수정하는 데 사용되는 주요 함수입니다. '함수명()'의 괄호는 함수의 인수를 '감쌉니다'.

#"형식 변경됨"

적용된 단계 목록에서 직전 단계를 참조합니다. 각 단계는 다음에 처리할 테이블의 버전을 알아야 합니다.

중괄호: { }

목록을 나타냅니다. 여러 열을 한 번에 변환할 수 있으므로, M에서는 이러한 중괄호 안에 지침을 포함해야 합니다.

고객명

변경할 특정 열을 지정합니다. 큰따옴표는 이름을 문자 그대로의 텍스트로 정의합니다. 다른 열에 동일한 단계를 적용하려면 다른 이름을 입력하면 됩니다.

텍스트를 입력하세요

열을 특정 데이터 형식으로 강제 지정합니다. 이를 통해 Excel이 이름을 숫자나 날짜로 잘못 식별하는 것을 방지합니다.

각

Power Query에 해당 열의 각 개별 행마다 다음 논리를 반복하도록 지시하는 키워드.

텍스트.적절

적용되는 특정 함수입니다. 이 경우, 텍스트를 제목 형식으로 변환합니다. 모든 글자를 대문자로 하려면, 이를 Text.Upper로 교체하세요.

언더스코어: _

이 셀의 현재 값을 나타내는 자리 표시자.

표준 Excel 수식과 달리 M 언어는 대소문자를 구분합니다. Table.TransformColumns 대신 table.transformcolumns를 작성하면 오류가 발생하므로 대문자와 소문자 사용에 주의하세요!

스니펫 2: 보이지 않는 서식 오류 제거

열의 레이아웃 정리

목적: CustomerName 열에서 선행 공백과 줄 바꿈을 제거하는 것입니다. 특히 줄 바꿈을 공백으로 대체하여 "jOHN [break] sMITH"가 "John Smith"가 되도록 하려는 것입니다. 이를 통해 "JohnSmith"가 되지 않도록 합니다.

이전과 같이 fx를 클릭하고, 기존 텍스트를 아래 코드로 바꾼 다음 Enter를 누르세요:

= Table.TransformColumns(FixCasing, {{"CustomerName", each Text.Clean(Text.Trim(Text.Replace(_, "#(lf)", " "))), type text}})

괄호 안의 첫 번째 요소는 항상 적용된 단계 목록의 이전 단계 이름과 일치해야 한다는 것을 기억하세요.

CleanTrim 단계를 이름 변경하세요.

각 주요 부분이 하는 일은 다음과 같습니다:

  • #(lf): 이것은 M에서 줄 바꿈(line feed)을 나타내는 축약형입니다(엑셀에서 Alt+Enter로 생성되는 보이지 않는 문자—줄 바꿈).
  • Text.Replace(_, "#(lf)", " "): 이는 줄 바꿈을 찾아서 공백으로 바꿉니다.
  • Text.Trim: 셀의 시작과 끝에 있는 불필요한 공백을 제거합니다.
  • Text.Clean: 남아 있는 비인쇄 제어 문자(숨겨진 ASCII 문자 등)를 제거합니다.

스니펫 3: 혼란스러운 문자열에서 숫자 추출

Text.Select로 문자 필터링하기

목적: OrderID 열에서 숫자 이외의 모든 것을 제거하는 것.

fx를 클릭한 후, 아래 코드를 붙여넣고 Enter 키를 누르세요:

= Table.TransformColumns(CleanTrim, {{"OrderID", each Text.Select(_, {"0".."9"}), type text}})

ExtractNumbers 단계를 이름 변경하세요.

여기서 도입한 새로운 요소들을 살펴봅시다:

  • Text.Select: M에게 나열된 특정 문자만 "선택"하고 나머지는 모두 버리라고 지시합니다.
  • {"0".."9"}: 이는 허용된 문자 목록을 정의합니다. 두 개의 점(..)은 "그리고 그 사이의 모든 것"을 의미하며, 즉 0에서 9까지 모든 숫자가 추출됩니다. 문자와 기호는 이 목록에 없으므로 완전히 삭제됩니다.

스니펫 4: 열 병합 및 'null' 오류 처리

깨끗한 ID-지역 코드를 생성하기

목적: 정리된 OrderID와 Region을 단일 코드(예: 101N)로 결합하는 것입니다. 만약 지역이 비어있다면(null), 'null'이라는 단어가 나타나지 않고 ID만 표시되도록 하고, 그 후 원래의 열들을 삭제하여 테이블을 간결하게 유지합니다.

fx를 클릭하고, 다음 코드를 삽입한 후 Enter를 누르세요:

= Table.SelectColumns(Table.AddColumn(ExtractNumbers, "ID", each [OrderID] & (if [Region] = null then "" else [Region])), {"ID", "CustomerName"})

단계를 MergeIDRegion로 이름을 바꾸세요.

이 코드가 작동하는 방법은 다음과 같습니다:

  • if [Region]=null then "" else [Region]: Power Query에게 Region을 살펴보라고 지시합니다. 만약 null을 보면, 이를 빈 문자열("")로 처리합니다. 이렇게 하면 103과 null 값을 결합할 때 103null이 아니라 103이 나오게 됩니다.
  • [OrderID] &...: 스니펫 3에서 정리된 숫자 ID를 가져오며, 앰퍼샌드(&)는 Power Query가 지역 텍스트를 그 끝에 바로 붙이도록 지시합니다.
  • "ID": 새로운 결합 열의 헤더 이름을 정의합니다.
  • {"ID", "CustomerName"}: 마지막에 이 두 열만 나열하면 Power Query에 원래의 OrderID와 Region 열을 버리라고 지시하는 것입니다.

M 언어가 단지 고급 수식들의 모음임을 알게 되면, 두려움은 사라집니다. 이 네 가지 스니펫은 효율성, 정확성으로 나아가는 첫 번째 단계이자, 간결하고 관리 가능한 코드를 사용하여 클릭 피로에서 벗어나는 방법입니다. 이제 개발자처럼 생각하기 시작했으니, 일상 작업에 더 큰 힘을 제공하는 도구인 Excel에서 Python을 사용하는 것으로 전환해 보는 것은 어떨까요.

관련기사