엑셀 파워 쿼리 기초: 데이터 합치고 정리하기

수식은 데이터를 셀 하나씩 정리하고, 다음 달이 되면 같은 일을 처음부터 다시 해야 해요. 파워 쿼리(Power Query)는 한 번만 정리하고, 그 과정의 클릭 하나하나를 레시피로 저장해 둬요. 다음 달 파일이 들어오면 새로 고침만 클릭하면 같은 단계가 같은 순서로 새 데이터에 적용돼요. 다시 입력할 건 없어요.

이 튜토리얼에서는 자주 하는 작업 하나로 그 레시피를 만들어 볼게요. 조금씩 지저분한 월별 판매 내보내기 파일 두 개를, 스스로 최신 상태를 유지하는 깔끔한 표 하나로 합치는 작업이에요.

예제 데이터: 월별 내보내기 파일 두 개

시스템이 매달 Date, Region, Revenue 세 열로 된 CSV 파일을 내보내요. 1월 파일에는 망가진 행이 하나 있어요. 날짜 자리에 bad-date가, 금액 자리에 N/A가 들어 있어요.

fx

	A	B	C	D	E	F	G
1	Date	Region	Revenue		Date	Region	Revenue
2	2026-01-05	East	4200		2026-02-03	East	3900
3	2026-01-08	West	3100		2026-02-14	Central	2800
4	bad-date	Central	N/A

두 파일을 다른 파일이 없는 폴더 하나에 넣으세요. 예를 들면 C:\Data\Monthly예요. 파워 쿼리는 폴더 자체를 원본으로 다루기 때문에, 나중에 추가한 파일도 자동으로 포함돼요.

폴더 전체 가져오기

1. 폴더 쿼리를 시작하세요

   데이터› then 데이터 가져오기 및 변환› then 데이터 가져오기› then 파일에서› then 폴더에서를 선택하세요. 폴더를 찾아 선택하고 열기를 클릭하세요.

2. 파일을 결합하세요

   폴더 안의 모든 파일이 이름, 확장명, 날짜와 함께 목록으로 나타나요. 결합 단추의 화살표를 클릭하고 데이터 결합 및 변환(Combine & Transform Data)을 선택하세요.

3. 샘플 파일을 확인하세요

   파일 결합(Combine Files) 대화 상자에 첫 번째 파일이 샘플로 나오고, 구분 기호와 미리 보기가 함께 보여요. 열이 제대로 나뉘었는지 확인하고 확인을 클릭하세요.

파워 쿼리 편집기(Power Query Editor)가 열리고, 두 파일의 행이 모두 들어 있는 표 하나가 보여요. 각 행이 어느 파일에서 왔는지 알려 주는 Source.Name 열도 함께 생겨요. 왼쪽 쿼리 창을 보면 기본 쿼리는 폴더 이름(여기서는 Monthly)으로 만들어져 있어요. 파워 쿼리는 샘플 파일 변환(Transform Sample File)을 비롯한 도우미 쿼리 묶음도 함께 만들어요. 여기에 추가한 단계는 파일을 쌓기 전에 파일마다 하나씩 실행돼요. 지금은 도우미 쿼리는 그대로 두고 기본 쿼리에서 작업하세요.

적용된 단계: 레시피

편집기 오른쪽의 쿼리 설정 창에 적용된 단계(Applied Steps) 목록이 있어요. 작업을 하나 할 때마다 이름이 붙은 단계가 목록 끝에 추가되고, 파일을 결합하면서 파워 쿼리가 몇 단계를 이미 넣어 두었어요.

• 단계를 클릭하면 그 시점의 데이터를 볼 수 있어요. 문제가 어디서 시작됐는지 찾을 때 이렇게 해요.
• 단계 옆의 X를 클릭하면 단계가 삭제되고, 톱니바퀴 아이콘(있는 단계만)을 클릭하면 설정을 바꿀 수 있어요.
• 단계를 마우스 오른쪽 단추로 클릭하면 이름을 바꾸거나, 삭제하거나, 위치를 옮길 수 있어요.

단계마다 바로 앞 단계의 결과를 받아서 동작하기 때문에 순서가 중요해요. 어떤 열을 쓰는 단계는 앞 단계에서 그 열을 지우거나 이름을 바꿨다면 실패해요. 그리고 아래에서 보겠지만, 오류를 만드는 형식 변경보다 먼저 오류를 제거하면 아무 일도 일어나지 않아요. 그 시점에는 오류가 아직 없거든요.

데이터 정리하기

1. 머리글을 확인하세요

   열 이름이 Date, Region, Revenue여야 해요. CSV 파일을 결합하면 파워 쿼리가 보통 각 파일의 첫 행을 머리글로 알아서 올려 줘요. 열 이름이 Column1, Column2로 나오고 실제 이름이 첫 행에 들어 있다면 홈› then 변환› then 첫 행을 머리글로 사용을 클릭하세요.

2. 데이터 형식을 지정하세요

   열 머리글 왼쪽의 아이콘이 그 열의 형식을 알려 줘요. ABC는 텍스트, 123은 정수, 달력 모양은 날짜예요. Date의 아이콘을 클릭해 날짜를, Revenue의 아이콘을 클릭해 정수를 고르세요. 기존 변환 단계를 바꿀지 새 단계를 추가할지 물으면 현재 항목 바꾸기를 고르세요.

   이제 1월의 망가진 행은 두 열 모두 Error로 표시돼요. bad-date는 날짜가 될 수 없고 N/A는 숫자가 될 수 없기 때문이에요.

   fx

   	A	B	C	D
   1	Source.Name	Date	Region	Revenue
   2	feb.csv	2026-02-03	East	3,900
   3	feb.csv	2026-02-14	Central	2,800
   4	jan.csv	2026-01-05	East	4,200
   5	jan.csv	2026-01-08	West	3,100
   6	jan.csv	Error	Central	Error

3. 오류가 있는 행을 제거하세요

   Date 머리글을 클릭하고 Ctrl을 누른 채 Revenue 머리글을 클릭하세요. 그다음 홈› then 행 감소› then 행 제거› then 오류 제거를 선택하세요. 깔끔한 행 네 개가 남아요.

여기서는 망가진 행에 남길 만한 값이 없으니 오류 제거가 맞는 선택이에요. 한 행에 잘못된 값이 하나뿐이라면, 열 머리글을 마우스 오른쪽 단추로 클릭해 오류 바꾸기(Replace Errors)를 쓰는 것도 생각해 보세요. 나머지 값은 살릴 수 있어요.

열 분할, 병합, 서식 바꾸기

자주 쓰는 다른 정리 명령도 위와 똑같이 단계를 하나씩 추가해요.

열 추가 탭의 열 병합과 서식도 같은 일을 하지만, 원래 열은 그대로 두고 새 열을 하나 추가해요.

레시피를 M 코드로 읽기

클릭 뒤에서 파워 쿼리는 M이라는 자체 언어로 스크립트를 작성해요. 보려면 홈› then 쿼리› then 고급 편집기를 클릭하세요. 파워 쿼리를 쓰는 데 M을 직접 작성할 필요는 없지만, 읽을 줄 알면 단계마다 정확히 무엇을 하는지 알 수 있어요.

파일 하나를 가져와서(데이터 가져오기 > 파일에서 > 텍스트/CSV에서) 같은 정리 작업을 한 쿼리라면 스크립트가 이렇게 생겼어요.

let
    Source = Csv.Document(File.Contents("C:\Data\Monthly\jan.csv"), [Delimiter = ",", Columns = 3, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}, {"Region", type text}, {"Revenue", Int64.Type}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Date", "Revenue"})
in
    #"Removed Errors"

let 아래의 한 줄이 적용된 단계 하나예요. 왼쪽 이름이 창에 보이는 단계 이름이고, 각 단계는 바로 앞 단계의 이름을 입력으로 받아요. 이렇게 사슬처럼 이어져 있어서 순서가 중요한 거예요. in 뒤의 이름이 쿼리가 돌려주는 결과예요. (한국어 엑셀에서는 단계 이름이 한국어로 만들어질 수 있지만 구조는 같아요.)

쿼리 추가와 쿼리 병합, 무엇을 쓸까?

파워 쿼리에서 쿼리를 합치는 방법은 두 가지이고, 둘 다 편집기의 홈 > 결합에 있어요. 해결하는 문제가 서로 달라요.

쿼리 추가(Append Queries)는 열 구성이 같은 표를 위아래로 쌓아요. 행이 늘어나요. 폴더 대신 1월과 2월 쿼리가 따로 있을 때 써요. 열은 이름으로 맞춰지기 때문에, 한쪽 표에만 있는 열은 다른 표에서 온 행에 null로 채워져요. 뒤에서 쓰이는 M 함수는 Table.Combine이에요.

let
    Source = Table.Combine({Jan, Feb})
in
    Source

쿼리 병합(Merge Queries)은 일치하는 열을 기준으로 두 표를 옆으로 이어 붙여요. 열이 늘어나요. 표 전체를 대상으로 하는 조회라고 생각하면 돼요. 지역별 담당 관리자를 정리한 Managers 쿼리가 하나 더 있다고 해 볼게요.

1. 병합을 시작하세요

   Monthly 쿼리를 선택한 상태에서 홈› then 결합› then 쿼리 병합의 화살표를 클릭하고 새 쿼리로 병합(Merge Queries as New)을 선택하세요. 그래야 원래 쿼리가 그대로 남아요.

2. 일치하는 열을 고르세요

   첫 번째 표는 이미 Monthly예요. 두 번째 표로 Managers를 고르고, 두 미리 보기에서 각각 Region 머리글을 클릭하세요. 조인 종류(Join Kind)는 왼쪽 외부(Left Outer)로 두고(첫 번째 표의 모든 행과 두 번째 표의 일치하는 행을 가져와요) 확인을 클릭하세요.

3. 새 열을 확장하세요

   새 쿼리에 표가 중첩된 Managers 열이 생겨요. 머리글의 확장 단추(화살표 두 개)를 클릭하고 Manager만 체크한 다음, 원래 열 이름을 접두사로 사용(Use original column name as prefix)의 체크를 해제하고 확인을 클릭하세요.

이제 판매 행마다 그 지역의 관리자가 붙어 있어요. 이 세 단계의 M 코드는 이래요.

let
    Source = Table.NestedJoin(Monthly, {"Region"}, Managers, {"Region"}, "Managers", JoinKind.LeftOuter),
    #"Expanded Managers" = Table.ExpandTableColumn(Source, "Managers", {"Manager"}, {"Manager"})
in
    #"Expanded Managers"

차이를 쉽게 기억하는 방법: 추가는 행을 늘리고, 병합은 열을 늘려요.

닫기 및 로드, 그리고 새로 고침

미리 보기가 제대로 보이면 홈› then 닫기› then 닫기 및 로드를 클릭하세요. 엑셀이 레시피를 실행하고, 결과를 새 워크시트에 엑셀 표로 넣어 줘요.

fx

	A	B	C	D
1	Source.Name	Date	Region	Revenue
2	feb.csv	2026-02-03	East	3,900
3	feb.csv	2026-02-14	Central	2,800
4	jan.csv	2026-01-05	East	4,200
5	jan.csv	2026-01-08	West	3,100

다른 곳에 넣고 싶다면 단추의 화살표를 클릭하고 닫기 및 다음으로 로드(Close & Load To)를 고르세요. 데이터 가져오기(Import Data) 대화 상자에서 결과를 표, 피벗 테이블 보고서, 피벗 차트, 연결만 만들기 중 하나로 넣을 수 있고, 이 데이터를 데이터 모델에 추가를 체크할 수도 있어요. 도우미 쿼리는 보통 연결만 만들어져서 통합 문서에 불필요한 시트가 생기지 않아요.

닫기 및 로드와 새로 고침은 헷갈리기 쉬워요.

• 닫기 및 로드(Close & Load)는 방금 고친 레시피를 저장하고, 편집기를 닫고, 결과를 로드해요. 단계를 바꾼 뒤에 써요.
• 새로 고침(Refresh)은 편집기를 열지 않고, 저장된 레시피를 현재 원본 파일에 다시 실행해요. 데이터가 바뀔 때마다 써요.

새로 고치려면 로드된 표를 마우스 오른쪽 단추로 클릭하고 새로 고침을 선택하거나, Ctrl+Alt+F5(데이터 탭의 모두 새로 고침)를 눌러 통합 문서의 모든 쿼리를 새로 고치세요. 폴더에 mar.csv를 넣고 새로 고치면, 같은 단계로 정리된 그 파일의 행이 표 아래쪽에 나타나요. 데이터 > 쿼리 및 연결(Queries & Connections)을 클릭하면 모든 쿼리가 나열된 창이 열리고, 쿼리를 두 번 클릭하면 다시 편집할 수 있어요.

자주 하는 실수

• 로드된 표에 직접 입력하기. 새로 고침은 원본에서 표를 다시 만들기 때문에, 로드된 셀을 손으로 고친 내용은 덮어써져요. 원본에서 데이터를 고치거나 쿼리에 단계를 추가하세요.
• 폴더 안의 다른 파일. 같은 폴더에 메모 파일이나 통합 문서가 섞여 있으면 그것까지 결합되고, 대개 오류가 나요. 폴더에는 내보내기 파일만 두거나, 쿼리의 첫 단계에서 파일 목록을 Extension 열로 필터링하세요.
• 원본의 열 이름 바꾸기. 단계는 열을 이름으로 가리켜요. 내보내기 파일에서 Revenue가 Amount로 바뀌면, Revenue를 쓰는 단계가 'Revenue' 열을 찾을 수 없다는 오류를 내요. 폴더 쿼리에서는 새 파일 하나만 열 이름이 바뀐 경우, 오류 대신 빈 값(null)으로 들어올 수도 있어요. 원본 머리글을 일정하게 유지하거나 단계를 고치세요.
• 오류를 너무 일찍 제거하기. 오류는 형식 변경이 실패할 때 생겨요. 오류 제거는 오류를 만드는 형식 변경 다음에 두세요. 그렇지 않으면 제거할 오류가 없어요.
• 폴더 옮기기. 폴더 경로는 쿼리의 Source 단계에 저장돼요. 폴더를 옮기거나 이름을 바꿨다면 데이터› then 데이터 가져오기 및 변환› then 데이터 가져오기› then 데이터 원본 설정에서 경로를 고치세요.

연습

연습

월별 파일 합치기

1. C:\Data\Monthly 같은 폴더를 만드세요. 메모장에서 아래 두 파일을 jan.csv, feb.csv라는 이름으로 그 폴더에 저장하세요.
2. 새 통합 문서에서 데이터 가져오기 > 파일에서 > 폴더에서로 폴더를 가져오고 데이터 결합 및 변환을 선택하세요.
3. 머리글을 확인하고, Date는 날짜, Revenue는 정수로 지정한 다음, 두 열에서 오류가 있는 행을 제거하세요.
4. 닫기 및 로드를 클릭하세요. 행 네 개, Revenue 합계 14,000이 나와야 해요.
5. 같은 머리글 줄 아래에 2026-03-02,West,3600, 2026-03-09,East,4100 두 행을 넣은 세 번째 파일 mar.csv를 저장하세요. 표를 새로 고치면 다른 작업 없이 행 여섯 개, 합계 21,700이 되어야 해요.

그다음 내 내보내기 파일로도 해 보세요. 열 구성이 같은 파일 묶음에 폴더 쿼리를 연결하고, 열 이름과 형식을 표준화한 다음, 깔끔한 표 하나로 로드해 보세요.

jan.csv
Date,Region,Revenue
2026-01-05,East,4200
2026-01-08,West,3100
bad-date,Central,N/A

feb.csv
Date,Region,Revenue
2026-02-03,East,3900
2026-02-14,Central,2800

실력 점검

• 데이터 가져오기로 폴더 안의 모든 파일을 표 하나로 가져올 수 있나요?
• 적용된 단계가 무엇인지, 그리고 형식을 바꾸기 전에 오류를 제거하면 왜 아무 일도 안 일어나는지 설명할 수 있나요?
• 열 구성이 같은 두 표를 하나로 합칠 수 있고, 언제 병합을 대신 써야 하는지 말할 수 있나요?
• 새로 고침과 닫기 및 로드의 차이를 설명할 수 있나요?