엑셀 파워 쿼리 엔지니어링: 매개 변수, 스테이징 쿼리, 새로 고침

처음 만든 파워 쿼리(Power Query) 가져오기는 대개 그걸 만들 때 쓴 파일에서만 잘 동작해요. 엔지니어링된 가져오기는 폴더가 옮겨져도, 다음 달 파일에 잘못된 값이 있어도, 다른 사람이 새로 고쳐도 계속 동작해요. 차이는 단계 수가 아니라 구조에 있어요. 경로를 바꾸는 곳은 한 곳, 파일을 읽는 함수는 하나, 공통 정리를 맡는 스테이징 쿼리도 하나, 그리고 들어오지 못한 행을 모두 보여 주는 표가 있는 구조예요.

예제: 매장별 내보내기 파일이 모이는 폴더

매달 매장별 판매 수량이 CSV 파일로 한 폴더에 들어와요. 지금까지 파일이 두 개 있어요. 1월 파일에 문제가 있는데, Eastside가 숫자 대신 N/A를 보고했어요.

fx

	A	B	C	D	E
1	Store	Units		Store	Units
2	Uptown	120		Westside	64
3	Midtown	85		Southgate	97
4	Eastside	N/A

다 만들고 나면 이런 쿼리 묶음이 생겨요.

fn과 stg 접두사는 엑셀이 요구하는 건 아니에요. 하지만 이렇게 붙여 두면 쿼리 창에서 어떤 쿼리가 부품이고 어떤 쿼리가 결과인지 한눈에 보여요.

하드 코딩된 경로를 매개 변수로 바꾸기

폴더를 가져오면 그 경로가 쿼리의 첫 단계에 그대로 입력돼요. 폴더를 옮기거나 통합 문서를 동료에게 넘기면, 그 경로가 들어 있는 쿼리가 모두 깨져요. 매개 변수(parameter)를 쓰면 경로를 이름 붙인 한 곳에 둘 수 있어요.

1. 매개 변수를 만드세요

   파워 쿼리 편집기에서 홈› then 매개 변수› then 매개 변수 관리› then 새 매개 변수를 선택하세요. 이름은 FolderPath, 형식은 텍스트, 제안 값은 모든 값으로 정하고, 현재 값에 C:\Data\StoreSales\ 같은 폴더 경로를 넣으세요. 설명도 적은 다음 확인을 클릭하세요.

2. 원본 단계에서 매개 변수를 쓰세요

   폴더를 가져오는 쿼리를 선택하고 원본(Source) 단계를 클릭하세요. 수식 입력줄이 보이지 않으면 보기› then 레이아웃› then 수식 입력줄로 켜세요. 따옴표로 묶인 경로를 매개 변수 이름으로 바꾸세요.

   = Folder.Files(FolderPath)
   

3. 경로는 한 곳에서만 바꾸세요

   가져오기가 다른 폴더를 가리키게 하려면 매개 변수 관리를 열고 현재 값을 바꾼 뒤 확인을 클릭하세요. FolderPath를 쓰는 모든 쿼리가 함께 따라가요.

고급 편집기에서 보면 매개 변수는 한 줄짜리 쿼리예요. 뒤에 붙은 메타데이터가 파워 쿼리에 이것이 매개 변수라고 알려 줘요.

"C:\Data\StoreSales\" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

매개 변수는 경로에만 쓰는 게 아니에요. 여러 쿼리가 필터 조건으로 쓰는 시작 날짜, 최소 금액, 지역 코드도 좋은 후보예요.

파일 하나를 읽는 사용자 지정 함수 쓰기

거의 똑같은 쿼리 다섯 개에 같은 단계를 반복해 두면, 파일 형식이 바뀔 때 다섯 군데를 고쳐야 해요. 사용자 지정 함수(custom function)는 한 번 작성해 두고 입력만 바꿔 호출하는 쿼리예요. LAMBDA를 M으로 구현한 것과 같아요.

1. 빈 쿼리를 시작하세요

   홈› then 새 쿼리› then 새 원본› then 기타 원본› then 빈 쿼리를 선택하세요. 쿼리 설정의 속성 > 이름에서 이름을 fnLoadStoreFile로 바꾸세요.

2. 함수를 작성하세요

   홈› then 쿼리› then 고급 편집기를 클릭하고, 내용을 모두 아래 코드로 바꾼 다음 완료를 클릭하세요.

   (fileContent as binary) as table =>
   let
       ParsedCsv = Csv.Document(fileContent, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.None]),
       PromotedHeaders = Table.PromoteHeaders(ParsedCsv, [PromoteAllScalars = true])
   in
       PromotedHeaders
   

   첫 줄 덕분에 이 쿼리는 함수가 돼요. 파일 내용(binary) 하나를 입력으로 받아 표를 돌려줘요. 쿼리 옆 아이콘이 fx로 바뀌어요.

사실 이 패턴은 직접 쓰지 않았을 뿐 이미 쓰고 있어요. 폴더에서 데이터 결합 및 변환(Combine & Transform Data)을 클릭하면, 엑셀이 같은 일을 하는 도우미 쿼리(샘플 파일 매개 변수, 샘플 파일 변환, 파일 변환 함수)를 만들어 주거든요. 함수를 직접 쓰면 원하는 이름을 붙일 수 있고, 읽기 쉬울 만큼 짧게 유지할 수 있고, 다른 폴더 쿼리에서도 다시 쓸 수 있어요.

한 번 스테이징하고, 여러 번 참조하기

스테이징 쿼리(staging query)는 공통 작업(결합, 필요한 열만 남기기, 형식 지정)을 하고 어디에도 로드되지 않아요. 데이터가 필요한 쿼리는 모두 이 쿼리에서 시작하고, 같은 단계를 반복하지 않아요.

빈 쿼리를 하나 더 만들어 이름을 stgStoreSales로 정하고, 고급 편집기에 다음 코드를 입력하세요.

let
    Source = Folder.Files(FolderPath),
    CsvFilesOnly = Table.SelectRows(Source, each Text.Lower([Extension]) = ".csv"),
    NameAndContent = Table.SelectColumns(CsvFilesOnly, {"Name", "Content"}),
    ParsedFiles = Table.AddColumn(NameAndContent, "Data", each fnLoadStoreFile([Content])),
    RemovedContent = Table.RemoveColumns(ParsedFiles, {"Content"}),
    ExpandedRows = Table.ExpandTableColumn(RemovedContent, "Data", {"Store", "Units"}),
    RenamedSource = Table.RenameColumns(ExpandedRows, {{"Name", "SourceFile"}}),
    KeptRawUnits = Table.DuplicateColumn(RenamedSource, "Units", "UnitsRaw"),
    TypedColumns = Table.TransformColumnTypes(KeptRawUnits, {{"SourceFile", type text}, {"Store", type text}, {"Units", Int64.Type}})
in
    TypedColumns

단계별로 읽어 볼게요. 폴더의 파일 목록을 가져오고, CSV 파일만 남기고(그래서 엉뚱한 메모 파일이 섞여도 가져오기가 깨지지 않아요), 파일마다 fnLoadStoreFile을 호출하고, 결과를 위아래로 쌓고, 계보를 위해 파일 이름을 남기고, Units의 텍스트 사본을 남긴 다음 형식을 지정해요. 단계 이름이 하는 일을 말해 주니 적용된 단계(Applied Steps) 목록이 그대로 문서가 돼요. 클릭으로 만든 단계도 같은 방식으로 이름을 바꿀 수 있어요. 단계를 마우스 오른쪽 단추로 클릭하고 이름 바꾸기를 고르세요.

ParsedFiles 단계는 코드 없이도 만들 수 있어요. NameAndContent 단계를 선택한 상태에서 열 추가› then 일반› then 사용자 지정 함수 호출을 선택하고, fnLoadStoreFile을 고른 뒤 입력으로 Content 열을 지정하세요.

fx

	A	B	C	D
1	SourceFile	Store	Units	UnitsRaw
2	sales-2026-01.csv	Uptown	120	120
3	sales-2026-01.csv	Midtown	85	85
4	sales-2026-01.csv	Eastside	Error	N/A
5	sales-2026-02.csv	Westside	64	64
6	sales-2026-02.csv	Southgate	97	97

스테이징 쿼리는 어디에도 로드되지 않아야 해요. 다음에 닫기 및 로드로 편집기를 닫으면 쿼리 및 연결(Queries & Connections) 창(데이터› then 쿼리 및 연결› then 쿼리 및 연결)을 열고, stgStoreSales를 마우스 오른쪽 단추로 클릭해 다음으로 로드를 고른 다음, 연결만 만들기를 선택하고 확인을 클릭하세요. 쿼리가 이미 시트에 로드되어 있었다면 그 데이터를 지울지 확인하는 메시지가 나와요. 확인을 클릭하세요.

스테이징 쿼리에서 새 쿼리를 시작하려면 편집기의 쿼리 창에서 stgStoreSales를 마우스 오른쪽 단추로 클릭하고 참조를 고르세요. 새 쿼리의 원본 단계는 = stgStoreSales 한 줄뿐이에요. (복제를 고르면 모든 단계가 복사되는데, 바로 그런 반복을 피하려는 거예요.)

잘못된 행을 Rejected Rows 쿼리로 보내기

오류 제거를 쓰면 보고서는 멀쩡해지지만 문제가 숨어 버려요. 합계가 소리 없이 줄어들고 아무도 이유를 몰라요. 대신 스테이징 결과를 쿼리 두 개로 나누세요.

stgStoreSales를 참조해서 새 쿼리를 만들고, 이름을 StoreSales로 정한 뒤 다음 단계를 넣으세요.

let
    Source = stgStoreSales,
    RemovedErrors = Table.RemoveRowsWithErrors(Source, {"Units"}),
    RemovedRawUnits = Table.RemoveColumns(RemovedErrors, {"UnitsRaw"})
in
    RemovedRawUnits

stgStoreSales를 한 번 더 참조해서 이름을 RejectedRows로 정하고, 첫 번째 쿼리가 버린 행만 누구나 읽을 수 있는 이유와 함께 남기세요.

let
    Source = stgStoreSales,
    RowsWithErrors = Table.SelectRowsWithErrors(Source, {"Units"}),
    AddedReason = Table.AddColumn(RowsWithErrors, "Reason", each "Units is not a whole number: " & [UnitsRaw], type text),
    RemovedUnits = Table.RemoveColumns(AddedReason, {"Units", "UnitsRaw"})
in
    RemovedUnits

스테이징 쿼리에서 UnitsRaw를 남겨 둔 이유가 이거예요. 형식을 바꾼 뒤에는 원래 텍스트가 그 사본에만 남거든요. 파워 쿼리 자체의 오류 메시지를 보여 주고 싶다면 Reason에 each (try [Units])[Error][Message]를 대신 쓰세요.

fx

	A	B	C
1	SourceFile	Store	Units
2	sales-2026-01.csv	Uptown	120
3	sales-2026-01.csv	Midtown	85
4	sales-2026-02.csv	Westside	64
5	sales-2026-02.csv	Southgate	97
6
7	SourceFile	Store	Reason
8	sales-2026-01.csv	Eastside	Units is not a whole number: N/A
9

RejectedRows는 QA 시트의 표로 로드하세요. 표가 비어 있으면 새로 고침이 깨끗하게 끝난 거예요. 비어 있지 않으면 담당자가 어느 파일의 어느 매장을 확인해야 하는지 바로 알 수 있고, 보고서가 아니라 원본을 고칠 수 있어요.

쿼리마다 로드 대상 고르기

데이터 가져오기(Import Data) 대화 상자(닫기 및 다음으로 로드, 또는 쿼리 및 연결 창의 다음으로 로드에서 열려요)에서 쿼리마다 결과를 어디에 둘지 정해요.

StoreSales가 측정값을 쓰는 피벗 테이블에 데이터를 공급한다면 데이터 모델(Data Model)에 넣는 게 맞아요. 보고서를 워크시트 수식으로 만든다면 표로 로드해도 돼요. 매개 변수와 함수는 아예 로드되지 않아요.

새로 고치고 결과 점검하기

Ctrl+Alt+F5(데이터› then 쿼리 및 연결› then 모두 새로 고침)로 전부 새로 고치세요. 그다음 빠진 것이 없는지 매번 확인하세요.

• 행 수 맞춰 보기. 깔끔한 쿼리의 행 수와 거부된 쿼리의 행 수를 더하면 파일의 행 수와 같아야 해요. 편집기 왼쪽 아래 상태 표시줄에 선택한 쿼리 미리 보기의 열 수와 행 수가 나와요. stgStoreSales는 5행이에요. 두 결과 쿼리를 모두 표로 로드했다면 QA 시트의 아래 수식이 같은 숫자를 돌려줘야 해요. 여기서는 5(깔끔한 행 4개와 거부된 행 1개)예요. 쿼리 결과가 비어 있어도 표에는 빈 행이 하나 남기 때문에 ROWS보다 COUNTA가 안전해요.

=COUNTA(StoreSales[Store])+COUNTA(RejectedRows[Store])복사

• 열 품질 보기. 편집기에서 보기› then 데이터 미리 보기› then 열 품질에 체크하세요. 열 머리글마다 유효, 오류, 비어 있는 값의 비율이 나와요. 기본적으로 처음 1,000행만 분석하니, 상태 표시줄 왼쪽 아래의 문구를 클릭해 전체 데이터 집합으로 바꾸세요.
• 오류뿐 아니라 빈 값도 살피기. 새 파일에 Units 열이 아예 없으면 그 행들의 Units는 null로 들어와요. 오류가 아니라서 두 쿼리 모두 잡아내지 못해요. 열 품질에서는 비어 있음으로 보여요.
• 무엇이 무엇에 데이터를 주는지 보기. 보기› then 종속성› then 쿼리 종속성을 선택하면 폴더에서 FolderPath, fnLoadStoreFile, stgStoreSales를 거쳐 두 결과 쿼리까지 이어지는 흐름이 그려져요.
• 고치기 전에 읽기. 스테이징 쿼리의 적용된 단계를 하나씩 클릭하면서, 행 수나 값이 처음으로 틀어지는 단계를 찾으세요.

새로 고침 시점을 정하려면 쿼리 및 연결 창에서 쿼리를 마우스 오른쪽 단추로 클릭하고 속성을 고르세요. 파일을 열 때 데이터를 새로 고칠지, 몇 분마다 새로 고칠지 정할 수 있어요.

자주 하는 실수

• 참조 대신 복제하기. 복제는 모든 단계를 복사하기 때문에, 한쪽을 고쳐도 다른 쪽에는 반영되지 않아요. 스테이징 쿼리를 바탕으로 하는 쿼리는 모두 참조로 만드세요.
• 스테이징 쿼리를 로드하기. 통합 문서에 데이터가 두 배로 들어가고, 엉뚱한 표로 보고서를 만들 빌미가 돼요. 연결만 만들기로 정하세요.
• 스테이징 쿼리에서 오류 제거하기. 그러면 뒤에 오는 어떤 쿼리도 거부된 행을 볼 수 없어요. 오류 처리는 결과 쿼리에서 하세요.
• Changed Type1, Filtered Rows2 같은 단계 이름 그대로 두기. 여섯 달 뒤에는 그 단계가 왜 있는지 아무도 몰라요. 하는 일이 드러나게 이름을 바꾸세요.
• 다른 곳에 경로를 하드 코딩하기. 다른 쿼리에 Folder.Files("C:\...")가 하나라도 더 있으면 매개 변수가 모든 것을 제어하지 못해요. 쿼리마다 고급 편집기에서 예전 경로를 찾아보세요.
• "잘 됐다"는 새로 고침을 그대로 믿기. 오류 메시지 없이 끝난 새로 고침에서도 행이 빠질 수 있어요. 매번 행 수와 RejectedRows 표를 확인하세요.

연습

연습

폴더 가져오기 엔지니어링하기

1. C:\Data\StoreSales\ 같은 폴더를 만들고, 맨 위에 나온 두 파일을 sales-2026-01.csv와 sales-2026-02.csv로 저장하세요(머리글 줄은 Store,Units).
2. 새 통합 문서에서 파워 쿼리 편집기를 열고(데이터› then 데이터 가져오기 및 변환› then 데이터 가져오기› then 파워 쿼리 편집기 시작) FolderPath 매개 변수를 만드세요.
3. 위 코드로 fnLoadStoreFile과 stgStoreSales를 만들고, 스테이징 쿼리를 연결만 만들기로 정하세요.
4. 스테이징 쿼리를 두 번 참조해서 StoreSales와 RejectedRows를 만들고, 둘 다 QA 시트에 표로 로드하세요. (실제 보고서라면 StoreSales는 보통 데이터 모델로 보내요.)
5. 새로 고치고 확인하세요. 깔끔한 행 4개의 수량 합계 366, Eastside의 거부된 행 1개, 그리고 COUNTA 점검 결과 5가 나와야 해요.
6. Uptown,110과 Midtown,abc가 들어 있는 sales-2026-03.csv를 추가하고 새로 고치세요. 깔끔한 행 5개(수량 476)와 거부된 행 2개가 나오고, 점검 결과는 7이어야 해요.
7. 폴더를 옮기고 FolderPath만 고친 다음 다시 새로 고치세요.

그다음 실제로 반복하는 가져오기 작업에 이 패턴을 적용해 보세요. 경로는 매개 변수로, 공통 작업은 연결만 만드는 스테이징 쿼리로, 실패한 행은 모두 눈에 보이는 표로 남기는 거예요.

실력 점검

• 하드 코딩된 파일 경로를 쿼리 매개 변수로 바꿀 수 있나요?
• 같은 단계를 쿼리 다섯 개에 반복하는 것보다 사용자 지정 함수가 나은 점을 설명할 수 있나요?
• 형식 변환에 실패한 행을 조용히 버리지 않고 별도의 표로 보낼 수 있나요?
• 스테이징 쿼리와 통합 문서로 로드되는 쿼리의 차이를 설명할 수 있나요?
• 새로 고친 뒤 빠진 행이 없다는 걸 증명할 수 있나요?