테이블 열을 확장할 때 Power Query 최적화

Power BI 사용자가 데이터를 빠르게 수집하고 흥미롭고 강력한 보고서를 생성하여 지능형 비즈니스 의사 결정을 내릴 수 있는 단순성과 사용 편의성을 통해 사용자는 성능이 저조한 쿼리를 쉽게 생성할 수 있습니다. 이 문제는 외래 키가 SQL 테이블 또는 SharePoint 목록과 관련된 방식으로 관련된 두 개의 테이블이 있는 경우에 종종 발생합니다. (레코드의 경우 이 문제는 SQL 또는 SharePoint와 관련이 없으며, 특히 스키마가 유동적이고 사용자 지정 가능한 많은 백 엔드 데이터 추출 시나리오에서 발생합니다.) 공통 키를 공유하는 별도의 테이블에 데이터를 저장하는 것은 본질적으로 문제가 되지 않습니다. 실제로 데이터베이스 디자인 및 정규화의 기본 신조입니다. 그러나 그것은 관계를 확장하는 더 나은 방법을 의미합니다.

SharePoint 고객 목록의 다음 예제를 고려해 보세요.

그리고 참조하는 다음 위치 목록입니다.

목록에 처음 연결할 때 위치가 레코드로 표시됩니다.

이 최상위 데이터는 모든 웹 디버거에서 볼 수 있는 SharePoint API에 대한 단일 HTTP 호출(메타데이터 호출 무시)을 통해 수집됩니다.

레코드를 확장하면 보조 테이블에서 조인된 필드가 표시됩니다.

한 테이블에서 다른 테이블로 관련 행을 확장할 때 Power BI의 기본 동작은 호출을 생성하는 것입니다 Table.ExpandTableColumn. 생성된 수식 필드에서 이를 확인할 수 있습니다. 아쉽게도 이 메서드는 첫 번째 테이블의 모든 행에 대해 두 번째 테이블에 대한 개별 호출을 생성합니다.

이렇게 하면 기본 목록의 각 행에 대해 HTTP 호출 수가 하나씩 증가합니다. 위의 5~6개 행 예제에서는 별로 보이지 않을 수 있지만 SharePoint 목록이 수십만 개의 행에 도달하는 프로덕션 시스템에서는 이로 인해 상당한 환경 저하가 발생할 수 있습니다.

쿼리가 이 병목 현상에 도달하면 가장 좋은 완화 방법은 클래식 테이블 조인을 사용하여 행당 호출 동작을 방지하는 것입니다. 이렇게 하면 두 번째 테이블을 검색하는 호출이 하나만 있고 나머지 확장은 두 테이블 간의 공통 키를 사용하여 메모리에서 발생할 수 있습니다. 경우에 따라 성능 차이가 크게 발생할 수 있습니다.

먼저 원래 테이블로 시작하여 확장하려는 열을 확인하고 항목의 ID를 확인하여 일치시킬 수 있도록 합니다. 일반적으로 외래 키의 이름은 ID가 추가된 열의 표시 이름과 유사합니다. 이 예제에서는 LocationId입니다.

둘째, 보조 테이블을 로드하여 외래 키인 ID를 포함해야 합니다. 쿼리 패널을 마우스 오른쪽 단추로 클릭하여 새 쿼리를 만듭니다.

마지막으로 일치하는 각 열 이름을 사용하여 두 테이블을 조인합니다. 일반적으로 이 필드는 먼저 열을 확장한 다음 미리 보기에서 일치하는 열을 찾아 찾을 수 있습니다.

이 예제에서는 주 목록의 LocationId가 보조 목록의 ID와 일치하는 것을 볼 수 있습니다. UI의 이름을 Location.Id 열 이름을 고유하게 만듭니다. 이제 이 정보를 사용하여 테이블을 병합해 보겠습니다.

쿼리 패널을 마우스 오른쪽 단추로 클릭하고 새 쿼리>병합>쿼리를 새로 만들기로 선택하면 이러한 두 쿼리를 결합하는 데 도움이 되는 친숙한 UI가 표시됩니다.

드롭다운에서 각 테이블을 선택하여 쿼리의 미리 보기를 확인합니다.

두 테이블을 모두 선택하면 테이블을 논리적으로 조인하는 열을 선택합니다(이 예제에서는 기본 테이블의 LocationId이고 보조 테이블의 ID임). 대화 상자는 해당 외래 키를 사용하여 일치하는 행 수를 지정합니다. 이러한 종류의 데이터에 기본 조인 종류(왼쪽 외부)를 사용할 수 있습니다.

확인을 선택하면 조인의 결과인 새 쿼리가 표시됩니다. 이제 레코드를 확장해도 백 엔드에 대한 추가 호출이 의미하지는 않습니다.

이 데이터를 새로 고치면 SharePoint에 대한 호출이 2개만 발생합니다. 하나는 기본 목록에 대한 호출이고 다른 하나는 보조 목록에 대한 호출입니다. 조인은 메모리에서 수행되므로 SharePoint에 대한 호출 수가 크게 줄어듭니다.

이 방법은 일치하는 외래 키가 있는 PowerQuery의 두 테이블에 사용할 수 있습니다.

참고 항목

SharePoint 사용자 목록 및 분류는 테이블로도 액세스할 수 있으며, 사용자가 이러한 목록에 액세스할 수 있는 적절한 권한이 있는 경우 위에서 설명한 대로 정확하게 조인할 수 있습니다.