파워 쿼리 사용자 지정 열의 M 코드 | 파워 BI

오늘의 블로그에서는 M 코드를 파워 쿼리 사용자 지정 열에 통합하기 위한 간단하고 일반화 가능한 프레임워크를 포함하여 LuckyTemplates 문제 #9에 대한 솔루션을 소개하겠습니다 . 이 블로그 하단에서 이 튜토리얼의 전체 비디오를 볼 수 있습니다.

M 코드를 사용하여 파워 쿼리에서 열을 만들 수 있으면 더 복잡한 문제를 해결하는 능력이 향상됩니다.

해결책을 찾기 전에 문제의 본질을 검토해 봅시다. 93년의 오스카 데이터 중 Kaggle의 빅 데이터 세트를 제공했습니다. 우리는 최고의 여배우, 수상자, 특히 두 개 이상의 여우주연상을 수상한 여성을 추출한 다음 두 번째 상을 수상한 연도를 Queryon 타임라인을 사용하여 시각화하고 싶었습니다.

이 문제를 종합할 때 여러 가지 목표가 있었습니다. 우리는 문제가 무엇에 집중하기를 원하며 주요 목표는 무엇입니까?

그리고 여기에는 세 가지가 있었습니다.

첫 번째는 Group By 및 All Rows 구문을 사용하여 몇 가지 연습을 배우거나 개발하는 것이었습니다 . 이것은 광범위한 문제를 매우 간단하게 해결하고 종종 매우 복잡한 DAX의 필요성을 제거하기 때문에 제가 정말 집중하고 있는 것입니다.

두 번째 목표는 특히 M 작업에 익숙하지 않은 사람들을 위한 것입니다. 기본 M 코드를 작성하는 데 약간의 편안함을 줄 수 있는 문제를 만들고 싶었습니다 . 나는 기본 M 코드를 개발하는 것이 어떤 것인지, 그리고 그것을 하는 과정에 대해 생각하는 방식을 강요하고 싶었습니다.

Gil Raviv의 Power Query에 관한 책의 한 장에 따르면 Power Query 문제의 약 40%는 사용자 인터페이스를 통해서만 해결할 수 있습니다. 그러나 몇 가지 기본 M 코드를 추가하고 사용자 정의 열을 생성하는 기능을 추가하면 그렇게 어렵지 않습니다. 그 비율을 80% 이상 높일 수 있습니다. 몇 가지 기본적이고 간단한 기술로 문제 해결 능력을 두 배로 늘릴 수 있습니다.

세 번째로 하고 싶은 것은 Queryon Timeline 커스텀 비주얼을 익숙하지 않은 사람들에게 소개하는 것입니다. 제가 정말 사랑하게 된 것 중 하나입니다. 나는 그것을 여러 다른 경우에 사용했고 데이터를 시각화하는 정말 독특한 방법을 제공한다고 생각합니다.

목차

• 금주의 문제에 대한 솔루션 접근 방식 #9
• 파워 쿼리에서 그룹화 기준 사용
• Table.AddIndexColumn M 코드 통합
• Queryon Timeline Custom Visual을 사용한 시각화
• 결론

금주의 문제에 대한 솔루션 접근 방식 #9

이 문제에 대해 생각하면서 사고 실험을 하고 싶었습니다. CSV 파일 대신 93장의 종이 쪽지(각 연도의 여우주연상 수상자가 적힌 종이)를 주면 어떻게 될까요? 솔루션은 어떻게 생겼을까요?

귀하의 솔루션은 아마도 아래에 있는 것과 비슷할 것입니다.

나는 모든 사람들이 그것과 비교적 비슷한 해결책을 가질 것이라고 생각합니다. 개념적 솔루션을 얻은 후에는 파워 쿼리에서 이러한 각 단계를 실행하는 방법을 알아내기만 하면 됩니다.

그래서 저는 이것을 개괄적인 개요로 받아들인 다음 이 개념적 솔루션을 파워 쿼리의 무언가로 조작하는 방법을 살펴보겠습니다. 우리가 사용할 접근 방식은 상주하는 Power Query 전문가인 Melissa가 Googling을 통해 학습하는 방법입니다.

이제 개념적 모델로 돌아가면 처음 두 단계에는 슬립을 여배우별로 그룹으로 분류하고 슬립이 ​​하나만 있는 그룹을 버리는 것이 포함됩니다. 그래서 우리는 카운트를 하고 싶습니다. 방법을 모르면 Google에 들어가 용어를 검색해 볼 수 있습니다. 이 예에서는 "그룹 인 파워 쿼리"를 시도했습니다. 이를 통해 이 그룹화를 수행하는 방법에 대해 알아야 할 모든 것을 찾을 수 있습니다.

파워 쿼리에서 그룹화 기준 사용

그런 다음 파워 쿼리로 이동하여 솔루션 운영을 시작할 수 있습니다. 제가 한 것은 이 오스카상 표에서 CSV 파일을 살펴보고 가져와 매년 여우주연상 수상자로 필터링한 것입니다. 따라서 이름과 영화 연도의 93개 행만 있습니다.

파워 쿼리로 이동하여 해당 테이블로 이동하면 가장 먼저 수행할 작업은 이 Group By 입니다 .

우리가 하는 일이 매우 간단 하더라도 약간 더 나은 인터페이스를 제공하기 때문에 항상 고급으로 가는 것을 좋아했습니다 .

Name 으로 그룹화하고 AllData 열을 만들어 보겠습니다 . 이 열은 All Rows가 됩니다. 집계만 반환하는 대신 집계를 반환하지만 원하는 모든 행은 그대로 유지됩니다.

Count 라는 또 다른 열이 있고 Count Rows 로 이동할 때 이 열이 필요합니다 . 우리는 각 더미에서 전표의 수를 찾고 싶습니다. 그런 다음 확인을 클릭합니다.

그런 다음 테이블의 이 공백을 클릭할 수 있으며 한 번만 슬립을 얻습니다.

하지만 카운트가 여러 개인 항목의 경우 모든 레코드를 가져오는 것을 볼 수 있습니다.

다음으로 (1) 항목을 필터링하려고 합니다. 그래서 우리는 기본적으로 이름이 하나뿐인 모든 더미를 폐기하고 있습니다. 1을 선택하고 확인을 클릭합니다.

그리고 우리는 이제 14개의 여러 상을 수상했습니다. 그리고 우리는 이제 14개의 여러 상을 수상했습니다. 이를 확장하면 Name을 제거할 수 있습니다. 이미 여기에 Name이 있고 year_film 만 유지하기 때문입니다 .

이들 중 일부는 여러 번 승리할 것이기 때문에 이제 30개 행으로 줄었습니다. 이미 1로 1을 버렸기 때문에 실제로 Count 열을 제거할 수도 있습니다 .

이제 각 그룹의 전표를 오름차순 날짜로 정렬한 다음 각 그룹 내에서 두 번째 발생 날짜를 선택하는 개념적 모델의 다음 단계로 이동합니다.

정렬은 쉽습니다. 파워 쿼리로 돌아가서 여배우, 날짜별로 오름차순 정렬을 수행할 수 있습니다.

그러면 1과 2가 표시됩니다.

그래서 우리는 잘 정렬되었고, 이제 우리는 각각에 대한 색인이나 순위를 갖고 싶고 각 더미에서 두 번째 것을 취할 수 있기를 원합니다.

Table.AddIndexColumn M 코드 통합

이제 순위나 인덱스만 검색할 수 있고 Microsoft 참조 가이드 로 이동할 수도 있는 "Google에서 학습" 접근 방식으로 돌아갑니다 . 저는 항상 사용하는 외부 도구를 만들어 LuckyTemplates에서 Microsoft 참조 가이드로 바로 연결해 줍니다.

Microsoft 참조 가이드에는 모든 항목이 기능별로 그룹화되어 있으며 필요한 항목을 검색할 수 있는 필터 상자가 있습니다. 이 경우 필터 상자에 Rank 또는 Index를 입력합니다. Rank에 대해서는 아무 것도 제공하지 않지만 Index에 대해서는 우리가 찾고 있는 것과 관련되고 적용 가능한 이 Table.AddIndexColumn 이 있습니다. 또한 이 기능을 사용하는 방법에 대한 몇 가지 예를 제공합니다.

이제 파워 쿼리로 이동하여 Actor별로 테이블을 다시 그룹화해 보겠습니다.

이를 통해 테이블별로 14명의 우승자를 얻습니다. Frances McDormand라는 테이블로 이동하면 테이블이 날짜별로 정렬되어 있음을 알 수 있습니다.

그런 다음 파워 쿼리 사용자 지정 열을 추가하고 M 코드를 입력하는 중요한 단계가 있습니다 . Table.AddIndexColumn 이 있고 AllData 필드가 추가됩니다 . 여기에서 새 열(텍스트)을 Index 로 호출 하고 Index를 1에서 시작하여 1씩 증가시킵니다.

그리고 우리는 여기서 이 완벽한 인덱스를 얻습니다.

이제 모든 항목이 Custom 에서 선택되었으므로 여기에서 AllData 열을 삭제할 수 있습니다 . 다음으로 Custom 열을 확장하고 Name을 빼면 됩니다. 여기에도 이미 Name이 있기 때문입니다. 그것으로 우리가 가진 것은 각각에 대한 번호가 매겨진 색인입니다.

마지막으로 Count에서 수행한 작업을 수행할 수 있습니다. 즉, 두 번째 승리에 있는 모든 항목을 필터링합니다.

그리고 우리는 간다. 우리의 해결책이 있습니다.

Queryon Timeline Custom Visual을 사용한 시각화

Queryon Visual 의 구현을 기반으로 하는 몇 가지 다른 작업이 있습니다 . 병합을 수행하고 사진 URL에 추가할 것입니다.

따라서 쿼리를 병합하고, Actresses 테이블을 가져와서 Name 을 병합합니다 . 우리는 14~14개의 행이 일치하는 것을 얻습니다.

그런 다음 여배우 열에서 확장하고 URL로 확장합니다.

이제 URL이 포함된 이 열이 있습니다.

Queryon Timeline에 대한 지식과 함께 제공되는 또 다른 사항이 있습니다. Queryon은 연도가 아닌 전체 날짜를 처리할 때 가장 잘 작동합니다 . 따라서 이것은 실험과 함께 제공되는 것이지만 연도 열을 가져와 예제에서 열을 만들 수 있습니다 . 여기서 우리가 하고 싶은 것은 연도를 날짜로 바꾸는 것입니다.

그러면 서식이 더 좋아집니다. 바로 알 수 있을 거라 기대하지 않는 내용이지만 Queryon을 사용하면 알 수 있습니다. 확인을 클릭하면 날짜 값으로 입력됩니다. 더 이상 필요하지 않기 때문에 인덱스 테이블을 삭제할 수 있으며 year_film 열의 이름을 Year 로 변경할 수 있습니다 .

이제 이것의 시각화 측면으로 넘어가겠습니다. LuckyTemplates 멤버 중 한 명인 Jose가 시각화에 정말 큰 역할을 한 것을 강조하고 싶습니다.

시각화를 위한 파워 쿼리로 돌아가서 Microsoft가 최근에 사용자 지정 시각적 개체를 인증하는 방식에 일부 변경 사항을 구현했다는 점을 언급할 가치가 있습니다 . 해당 인증의 일환으로 외부 URL 이미지 사용을 강화했습니다.

App Source(버전 1.05)에 있는 Queryon Timeline 버전을 실제로 사용하는 경우 이러한 URL 이미지를 가져올 수 없습니다. 따라서 이제 이 1.04를 사용하여 URL 이미지를 유지하고 사이트에 바이너리 64 이미지를 PBIX 파일에 직접 삽입하는 방법을 사용할 수 있습니다.

이제 해당 연도를 날짜 필드로 변경한다고 언급했으므로 이를 데이터 모델에 연결해야 합니다.

일에서 일로 , 일에서 다로 변경 하고 Cross 필터에서 단일 방향으로 변경한 다음 이 관계를 활성 으로 만듭니다 .

다음으로 이미지 URL 필드의 경우 데이터 카테고리에서 미분류가 아니라 이미지 URL 로 표시되는지 확인해야 합니다 .

이제 모든 것이 잘 준비되었으므로 Name , Date 및 URL 필드를 끌어다 놓습니다 . 나머지는 형식 옵션에서 정말 간단한 정리가 됩니다.

파워 쿼리 테이블: 열을 동적으로 병합하는 방법
LuckyTemplates에서 쿼리를 병합하는 방법
파워 쿼리의 레코드 및 목록에서 값 추출

결론

여러분의 레퍼토리에 M 코드를 추가하는 일에 흥미를 느끼셨기를 바랍니다. 개념적 모델을 설정하고 Learn by Googling 접근 방식을 구현하는 방법을 보여 드렸습니다. M 코드를 사용자 지정 열에 통합하고 Queryon Timeline을 사용하여 결과를 시각화하는 단계를 안내했습니다.

도움이 되었기를 바랍니다. 튜토리얼에 대한 자세한 내용은 아래의 전체 비디오를 시청하고 관련 콘텐츠에 대한 자세한 내용은 아래 링크를 확인하십시오.

모두 제일 좋다!