Microsoft Excel에서 무료 Power Query 추가 기능의 도구를 이미 사용하기 시작한 경우, 곧 소스 데이터에 대한 링크를 지속적으로 끊는 것과 관련된 매우 전문적이지만 매우 빈번하고 성가신 문제에 직면하게 될 것입니다. 문제의 본질은 쿼리에서 외부 파일이나 폴더를 참조하는 경우 Power Query가 쿼리 텍스트에서 이들에 대한 절대 경로를 하드코딩한다는 것입니다. 컴퓨터에서는 모든 것이 잘 작동하지만 동료에게 요청이 포함된 파일을 보내기로 결정하면 동료가 실망할 것입니다. 그들은 컴퓨터의 원본 데이터에 대한 다른 경로를 가지고 있으며 쿼리가 작동하지 않습니다.
그런 상황에서 어떻게해야합니까? 이 경우를 다음의 예를 통해 좀 더 자세히 살펴보자.
문제의 공식화
폴더에 있다고 가정합니다. E:판매 보고서 파일이 거짓말 상위 100개 제품.xls, 회사 데이터베이스 또는 ERP 시스템(1C, SAP 등)에서 업로드한 파일입니다. 이 파일에는 가장 인기 있는 상품에 대한 정보가 포함되어 있으며 내부는 다음과 같습니다.
Excel에서 데이터가 포함된 빈 행, 병합된 셀, 추가 열, 다단계 머리글 등이 방해가 되는 형식으로 Excel에서 작업하는 것이 거의 불가능하다는 것이 바로 명백할 것입니다.
따라서 같은 폴더에 있는 이 파일 옆에 다른 새 파일을 만듭니다. 핸들러.xlsx, 여기서 소스 업로드 파일에서 못생긴 데이터를 로드하는 파워 쿼리 쿼리를 만듭니다. 상위 100개 제품.xls, 순서대로 나열하십시오.
외부 파일에 요청하기
파일 열기 핸들러.xlsx, 탭에서 선택 Data 명령 데이터 가져오기 – 파일에서 – Excel 통합 문서에서 (데이터 — 데이터 가져오기 — 파일에서 — Excel에서), 소스 파일의 위치와 필요한 시트를 지정하십시오. 선택한 데이터가 파워 쿼리 편집기에 로드됩니다.
정상으로 되돌리자:
- 다음을 사용하여 빈 줄 삭제 홈 — 라인 삭제 — 빈 라인 삭제 (홈 — 행 제거 — 빈 행 제거).
- 를 통해 불필요한 상위 4개 라인 삭제 홈 — 행 삭제 — 상위 행 삭제 (홈 — 행 제거 — 맨 위 행 제거).
- 버튼을 사용하여 첫 번째 행을 테이블 머리글로 올립니다. 첫 번째 줄을 헤더로 사용 탭 홈 (홈 — 첫 번째 행을 헤더로 사용).
- 명령을 사용하여 두 번째 열의 제품 이름에서 XNUMX자리 기사를 구분합니다. 분할 열 탭 변환 (변환 — 열 분할).
- 더 나은 가시성을 위해 불필요한 열을 삭제하고 나머지 열의 머리글 이름을 바꿉니다.
결과적으로 다음과 같은 훨씬 더 즐거운 그림을 얻을 수 있습니다.
이 고귀한 테이블을 파일의 시트에 다시 업로드해야 합니다. 핸들러.xlsx 팀 닫고 다운로드 (홈 — 닫기 및 로드) 탭 홈:
요청에서 파일 경로 찾기
이제 간결한 이름 "M"을 사용하여 파워 쿼리에 내장된 내부 언어에서 쿼리가 "내부에서" 어떻게 보이는지 살펴보겠습니다. 이렇게 하려면 오른쪽 창에서 쿼리를 두 번 클릭하여 쿼리로 돌아갑니다. 요청 및 연결 그리고 탭에서 검토 선택 고급 편집기 (보기 — 고급 편집기):
열리는 창에서 두 번째 줄은 원본 업로드 파일에 대한 하드 코딩된 경로를 즉시 나타냅니다. 이 텍스트 문자열을 매개변수, 변수 또는 이 경로가 미리 작성된 Excel 시트 셀에 대한 링크로 바꿀 수 있다면 나중에 쉽게 변경할 수 있습니다.
파일 경로가 있는 스마트 테이블 추가
지금은 파워 쿼리를 닫고 파일로 돌아가겠습니다. 핸들러.xlsx. 새 빈 시트를 추가하고 소스 데이터 파일의 전체 경로가 기록될 유일한 셀에 작은 "스마트" 테이블을 만들어 보겠습니다.
일반 범위에서 스마트 테이블을 만들려면 바로 가기 키를 사용할 수 있습니다. Ctrl 키+T 또는 버튼 표로 형식 지정 탭 홈 (홈 — 표 형식). 열 머리글(셀 A1)은 무엇이든 될 수 있습니다. 또한 명확성을 위해 테이블 이름을 지정했습니다. 파라미터 탭 건설자 (디자인).
익스플로러에서 경로를 복사하거나 수동으로 입력하는 것도 물론 특별히 어렵지는 않지만 인적 요소를 최소화하고 가능하면 자동으로 경로를 결정하는 것이 가장 좋습니다. 이것은 표준 Excel 워크시트 기능을 사용하여 구현할 수 있습니다. CELL (셀), 현재 파일의 경로를 포함하여 인수로 지정된 셀에 대한 유용한 정보를 제공할 수 있습니다.
소스 데이터 파일이 항상 프로세서와 동일한 폴더에 있다고 가정하면 필요한 경로는 다음 공식으로 구성할 수 있습니다.
=LEFT(CELL("파일 이름");FIND("[";CELL("파일 이름"))-1)&"상위 100개 제품.xls"
또는 영어 버전:
=LEFT(CELL(«파일 이름»);FIND(«[«;CELL(«파일 이름»))-1)&»Топ-100 товаров.xls»
... 기능은 어디에 있습니까 레브심브 (왼쪽) 전체 링크에서 여는 대괄호(즉, 현재 폴더의 경로)까지의 텍스트를 가져온 다음 소스 데이터 파일의 이름과 확장자가 여기에 붙습니다.
쿼리의 경로 매개변수화
마지막이자 가장 중요한 터치가 남아 있습니다. 요청에 소스 파일의 경로를 쓰는 것입니다. 상위 100개 제품.xls, 생성된 "스마트" 테이블의 A2 셀 참조 파라미터.
이렇게 하려면 파워 쿼리 쿼리로 돌아가서 다시 열어 보겠습니다. 고급 편집기 탭 검토 (보기 — 고급 편집기). 따옴표로 묶인 텍스트 문자열 경로 대신 "E:판매 보고서상위 100개 제품.xlsx" 다음 구조를 소개하겠습니다.
Excel.CurrentWorkbook(){[Name=”설정”]}[콘텐츠]0{}[소스 데이터 경로]
그것이 무엇으로 구성되어 있는지 봅시다.
- Excel.CurrentWorkbook() 현재 파일의 내용에 액세스하기 위한 M 언어의 기능입니다.
- {[Name=”설정”]}[콘텐츠] – 이것은 "스마트" 테이블의 내용을 가져오기를 원함을 나타내는 이전 함수에 대한 개선 매개변수입니다. 파라미터
- [소스 데이터 경로] 테이블의 열 이름입니다. 파라미터우리가 참조하는
- 0{} 테이블의 행 번호입니다. 파라미터데이터를 가져오고자 합니다. 캡은 계산되지 않으며 번호는 XNUMX이 아닌 XNUMX부터 시작합니다.
그게 다야, 사실.
클릭하는 것이 남아 있습니다. 마감재
우리의 요청이 어떻게 작동하는지 확인하십시오. 이제 두 파일이 포함된 전체 폴더를 다른 PC로 보낼 때 요청이 계속 작동하고 데이터 경로를 자동으로 결정합니다.
- 파워 쿼리란 무엇이며 Microsoft Excel에서 작업할 때 왜 필요한가요?
- 부동 텍스트 조각을 파워 쿼리로 가져오는 방법
- 파워 쿼리를 사용하여 XNUMXD 크로스탭을 플랫 테이블로 재설계