LuckyTemplates에 Excel의 여러 시트 추가

이 자습서에서는 여러 시트가 포함된 Excel 파일을 하나의 LuckyTemplates 테이블로 가져오고 여는 방법에 대해 설명합니다.

이 데이터 Excel 파일은 이 튜토리얼에서 사용됩니다.

파일에는 레이아웃이 동일한 60개의 데이터 시트가 포함되어 있습니다.

이 자습서의 목적은 모든 Excel 시트를 LuckyTemplates에 로드하고 Bond 정보를 하나의 테이블로 검색하는 것입니다. 보안 티커를 가져와 별도의 열에 추가해야 합니다. 그런 다음 UI를 활용하여 필요한 코드를 생성하는 단일 테이블에 모두 함께 추가합니다.

목차

• Excel 시트를 LuckyTemplates로 가져오기
• Excel에서 LuckyTemplates로 보안 티커 가져오기
• 빈 행 제거
• 쿼리 복제
• 열에 데이터 유형 할당
• Excel에서 LuckyTemplates로 보안 티커 열 추가
• 채권 조회를 위한 사용자 지정 열 만들기
• 결론

Excel 시트를 LuckyTemplates로 가져오기

가장 먼저 할 일은 파워 쿼리 편집기를 열고 매개 변수 관리 옵션 을 사용하여 파일 위치를 저장할 매개 변수를 만드는 것 입니다 .

매개 변수 관리 상자 내에서 새로 만들기를 클릭하고 FileLocation을 매개 변수 이름으로 입력합니다. 그런 다음 데이터 유형을 텍스트 로 설정 하고 제안된 값을 모든 값 으로 설정합니다 . Excel 파일의 파일 경로를 복사하여 현재 값 필드에 붙여넣습니다.

확인을 누른 후 새 원본을 클릭 하고 Excel 통합 문서를 선택합니다 .

그런 다음 Excel 파일을 선택하여 엽니다. 완료되면 네비게이터 화면이 열리고 Excel 파일 내의 모든 시트가 나열됩니다.

첫 번째 시트를 선택하고 확인을 누릅니다. 그런 다음 Power Query Editor 내부에 테이블이 표시됩니다.

다음으로 할 일은 하드 코딩된 파일 경로를 파일 매개변수로 변경하는 것입니다. 고급 편집기 창을 열고 소스 코드의 파일 경로를 FileLocation 으로 변경합니다 .

데이터를 파워 쿼리 로 가져오기 전에 경계 또는 데이터 범위가 정의되기 때문에 데이터를 Excel 테이블에 두는 것이 가장 좋습니다 . 파일에는 시트가 포함되어 있고 테이블이 없기 때문에 빈 열과 빈 행을 가져올 위험이 있으므로 이를 정렬해야 합니다.

Excel에서 LuckyTemplates로 보안 티커 가져오기

시트의 레이아웃이 고정되어 있다는 것을 알면 특히 사용자 인터페이스를 최대화하고 사용하여 코드를 생성하려는 경우 솔루션을 구축하는 데 도움이 됩니다. 예를 들어 한 가지 요구 사항은 보안 티커가 포함된 열을 추가하는 것입니다. 데이터를 보면 티커를 볼 수 있습니다.

사용자 인터페이스는 시세 값을 추출하는 데 도움이 될 수 있습니다. 셀을 마우스 오른쪽 버튼으로 클릭하고 새 쿼리로 추가 를 선택합니다 .

수식 입력줄에서 테이블 이름 뒤에 중괄호 사이에 0부터 시작하는 행 번호가 오는 것을 볼 수 있습니다. 중괄호는 위치 인덱스 연산자 라고 합니다 . 필드 액세스 연산자 라고 하는 대괄호로 묶인 필드 이름도 표시됩니다 .

해당 코드를 사용하여 이제 값을 추출할 수 있습니다. Bond 테이블로 돌아가 Bond 데이터를 가져옵니다. 먼저 상위 8개 8개 행을 제거합니다. 행 제거를 클릭 하고 상위 행 제거를 선택합니다 .

그런 다음 Number Of Rows 필드에 8을 입력하고 OK를 누릅니다.

그런 다음 첫 번째 행을 머리글로 사용을 클릭하여 머리글을 설정합니다.

완료되면 Bond 테이블은 이제 다음과 같이 표시됩니다.

머리글 바로 아래에 열 품질을 나타내는 가는 선이 표시됩니다. 거기에서 열에 꽤 많은 공백이 있음을 알 수 있습니다. 이것은 파일이 빈 행을 많이 가져왔음을 의미합니다.

빈 행 제거

빈 행을 제거하려면 행 제거를 클릭하고 빈 행 제거를 선택합니다 .

해당 변환은 다음 구문을 생성합니다.

Record.FieldValues는 테이블의 현재 행에서 모든 값을 목록으로 가져옵니다. List.RemoveMatchingItems는 두 번째 목록과 일치하는 항목이 있는 첫 번째 목록의 모든 값을 제거합니다. 두 번째 목록에는 빈 텍스트 문자열이나 null만 포함됩니다. 첫 번째 목록에서 제외될 값입니다.

모든 빈 텍스트 문자열과 null이 레코드 필드 값이 있는 목록에서 제거된 경우 목록은 비어 있어야 하며 List.IsEmpty는 True 로 평가됩니다. 그런 다음 Table.SelectRows는 True를 유지합니다.

빈 행만 있는 테이블로 끝나서는 안 됩니다. 이것이 List.IsEmpty 앞에 not 키워드가 추가된 이유입니다 . 그런 다음 비어 있지 않은 행을 포함하는 테이블을 반환합니다.

빈 행 외에 빈 열도 제거해야 합니다. 하지만 그 전에 열을 제거할 때 파워 쿼리가 생성하는 내용을 살펴보세요. 네 번째 열을 선택하고 열 제거를 클릭합니다 .

변환을 수행한 후 수식 입력줄에 이 구문이 표시됩니다.

Table.RemoveColumns 함수를 호출한 다음 적용된 단계 창의 이전 단계를 참조하고 첫 번째 인수로 전달합니다. 변환은 제거하려는 열의 열 이름이 포함된 목록을 추가로 전달합니다.

쿼리 복제

이제 쿼리를 복제하고 Applied Steps 창에서 Promote Headers 단계를 선택합니다. 그런 다음 해당 단계를 마우스 오른쪽 버튼으로 클릭하고 끝날 때까지 삭제 를 선택합니다 .

위치 인덱스 연산자를 사용하여 중괄호 세트에 0부터 시작하는 행 번호를 전달할 수 있습니다. 따라서 수식 입력줄에서 두 개의 중괄호 안에 0을 입력하여 첫 번째 행만 유지합니다 .

완료되면 Bond 테이블은 다음과 같이 표시됩니다.

여기에서 Removed Blank Rows 단계에서 생성된 로직의 일부를 재사용하여 레코드를 목록으로 전환하고 null을 제거합니다. Bond 쿼리로 돌아가서 빈 행 제거 단계를 선택합니다. 다음으로 이 M 코드를 복사합니다.

복제된 쿼리로 돌아가 수식 입력줄에 코드를 붙여넣습니다. 그런 다음 수식이 다음과 같이 보이도록 문자열을 정렬합니다.

이제 유지하려는 열 이름이 있는 목록을 만들었습니다. 쿼리의 이름을 Column Names 로 바꿉니다 .

그런 다음 Bond 쿼리로 돌아갑니다. 유지하려는 모든 열이 포함된 쿼리를 만들었으므로 Table.RemoveColumns 함수에서 제외하려는 모든 열을 나열해야 합니다.

Removed Blank Rows 단계에서 복사한 동일한 구문으로 {Column4} 를 변경합니다 . {“”, null} 도 ColumnNames 로 변경합니다 .

그런 다음 Bond 테이블의 실제 열 이름 목록을 전달해야 합니다. Record.FieldValues(_)를 Table.ColumnNames() 로 변경합니다 . 괄호 안에 #”Removed Blank Rows”를 입력하여 테이블 참조 인수를 전달합니다.

열에 데이터 유형 할당

다음으로 할 일은 열에 적절한 데이터 유형을 지정하는 것입니다. 날짜 열의 경우 머리글 옆에 있는 아이콘을 클릭하고 날짜를 선택합니다.

PX_LAST 및 YLD_YTM_MID 열의 경우 10진수 데이터 유형을 선택합니다.

이 세 가지 쿼리를 사용하여 Excel 파일의 모든 시트를 처리하는 솔루션을 디자인하는 데 사용할 수 있는 모든 빌딩 블록을 만들었습니다 . 이를 위해 Bond 쿼리를 복제하고 Applied Steps 창에서 Source 단계를 제외한 모든 단계를 삭제해야 합니다.

Excel에서 LuckyTemplates로 보안 티커 열 추가

소스 단계에서는 Excel 파일 내의 모든 데이터를 볼 수 있습니다. 각 시트에 대해 별도의 쿼리를 만드는 대신 Bond 쿼리를 사용하고 데이터 열에서 중첩 테이블을 변환할 수 있습니다.

먼저 보안 티커를 추가합니다. 표의 셀 안의 공백을 클릭하면 해당 표의 내용을 미리 볼 수 있습니다.

보안 티커를 가져오려면 문을 사용하여 논리를 만들어야 합니다 . Column2 쿼리로 이동하여 수식 입력줄에서 보안 티커의 주소를 복사합니다.

그런 다음 Bond 쿼리로 돌아가서 사용자 지정 열을 추가합니다.

열 보안 티커의 이름을 지정하고 다음 M 코드를 작성합니다.

이 수식에는 Security라는 단어가 Column1에 있으면 인접한 Column2의 셀 값을 제공한다는 IF 문이 있습니다. 그렇지 않으면 null이 제공됩니다.

확인을 누르면 보안 티커가 있는 새 열이 테이블에 추가됩니다.

Security Ticker 열의 드롭다운 버튼을 클릭하고 null을 선택 취소하여 열의 모든 null을 제거합니다.

그 후, 모든 시트에서 본드의 모든 정보가 남게 됩니다. Bond(2) 쿼리에서 수행된 변환을 반복하고 데이터 열의 중첩 테이블에 적용하기만 하면 됩니다.

채권 조회를 위한 사용자 지정 열 만들기

Bond(2) 쿼리로 이동하여 고급 편집기 창을 열고 다음 코드를 복사합니다.

그런 다음 Bond 쿼리로 돌아가서 다른 사용자 지정 열을 만듭니다. 여러 단계에 걸쳐 여러 변환을 적용해야 하므로 let 문을 사용해야 합니다 . 따라서 let을 입력 하고 고급 편집기에서 코드를 붙여넣습니다.

그런 다음 Bond_Sheet를 Data 로 변경하여 Data 열의 테이블을 변환합니다.

확인을 누르면 테이블에 새 열이 추가됩니다.

해당 열의 셀 안의 공백을 클릭하면 Bond(2) 쿼리의 데이터가 표시됩니다.

Bond 쿼리의 마지막 두 열만 있으면 됩니다. 따라서 Security Ticker 및 Custom 열을 모두 선택하고 열 제거 리본에서 다른 열 제거를 클릭합니다.

사용자 지정 열을 확장하고 원래 이름을 접두사로 사용 상자를 선택 취소합니다.

완료되면 테이블의 유효성을 검사합니다. 테이블 아래에서 전체 데이터 집합 기반 열 프로파일링 옵션을 선택합니다.

해당 변환 후 데이터 열에서 오류가 발생합니다.

따라서 데이터 열을 클릭하고 행 유지 리본에서 오류 유지를 선택하여 오류를 검사합니다.

데이터 열에서 오류 값을 클릭하면 아래 오류 메시지를 읽을 수 있습니다.

문제를 해결하려면 먼저 Kept Errors 단계를 제거하십시오. 데이터 열을 클릭하고 행 제거 리본에서 오류 제거를 선택합니다.

그런 다음 열 프로파일링 옵션을 다시 상위 1000개 행으로 설정합니다. 그리고 그게 다야!

결론

이 사용자 인터페이스 기반 솔루션은 Excel 파일의 여러 시트를 LuckyTemplates에 추가하는 데 도움이 될 수 있습니다. 60개의 개별 쿼리를 생성하고 모든 변환을 반복적으로 수행하는 대신 이 솔루션은 모든 변환을 수행하는 단일 쿼리를 생성할 수 있습니다. 이 솔루션을 활용하고 극대화하여 훌륭한 데이터 보고서를 작성 하십시오 .

여자 이름