LuckyTemplates 파워 쿼리: 중복 행 제거

이 블로그에서는 조건에 따라 LuckyTemplates 파워 쿼리에서 중복 행을 제거하는 방법을 보여드리겠습니다. 이 블로그 하단에서 이 튜토리얼의 전체 비디오를 볼 수 있습니다.

에서 제기된 질문에서 가져온 것입니다 . 구성원이 트랜잭션 데이터를 가지고 있으며 서로를 취소하는 행을 제거하려고 합니다.

수신용과 발신용의 두 가지 트랜잭션 유형이 있습니다. 그녀는 또한 이동 횟수를 추가했습니다. 그리고 그것을 바탕으로 그녀는 세 가지 시나리오를 만들었다.

시나리오 1은 두 행을 기반으로 합니다. 서로 상쇄되면 두 행을 모두 제거해야 합니다. 그렇지 않은 경우 두 행을 모두 유지해야 합니다.

시나리오 2와 3은 부분적으로 서로를 상쇄하는 행 조합을 기반으로 합니다. 이러한 행은 가장 최근에 들어오는 트랜잭션만 유지되도록 선입선출 방식으로 제거해야 합니다.

기본적으로 우리는 같은 논리를 계속해서 반복하고 있습니다. 우리는 이러한 모든 시나리오에 맞는 단일 접근 방식을 설계할 수 있어야 합니다.

우리가 할 일 중 일부는 절대 수량으로 열을 추가하고 "수량 균형 열 추가" 및 "모든 행" 옵션을 사용하여 중첩된 테이블로 그룹화하는 것입니다. 또한 이러한 중첩 테이블을 변환하고, 이동 유형 및 게시 날짜를 정렬하고, 누계를 생성하고, 조건에 따라 행만 유지합니다.

여기에서 수행할 수 있는 대부분의 변환은 사용자 인터페이스를 사용하는 것이므로 중간 수준으로 평가했습니다. 이제 LuckyTemplates 파워 쿼리로 이동하겠습니다.

목차

• 파워 쿼리에서 절대 수량으로 열 추가
• ID 인덱스 및 절대 수량으로 그룹화
• LuckyTemplates 파워 쿼리에서 중첩 테이블 변환
  • 이동 유형 및 게시 날짜 정렬
  • 누적 합계 만들기
  • 조건에 따라 행 필터링
• 결론

파워 쿼리에서 절대 수량으로 열 추가

절대 수량의 열을 추가하여 시작하겠습니다. Quantity 열을 선택한 다음 Add Column 리본 또는 탭 에서 Scientific 으로 이동한 다음 Absolute Value 를 클릭합니다 .

수식 입력줄에서 이 열의 이름을 바꾸겠습니다.

ID 인덱스 및 절대 수량으로 그룹화

이제 ID 인덱스와 절대 수량으로 그룹화할 수 있습니다. ID Index 를 선택한 다음 Shift 또는 Ctrl 키를 누른 상태에서 다중 선택하고 Abs qty (절대 수량)를 클릭합니다.

변환 탭에서 그룹 기준을 찾을 수 있습니다 .

그러나 홈 탭에서도 찾을 수 있습니다.

그것을 클릭하면 이제 새 열을 추가할 것입니다. "개수"가 아니라 양의 "합계"가 될 것입니다. 이름을 Qty (수량) Balance 로 바꾸겠습니다 . 수량 열의 합계가 됩니다. 그런 다음 다른 집계를 추가하지만 특별한 집계가 될 것이므로 All Rows 를 선택합니다 . 또한 이 열에 이름( AllRows ) 을 지정해 보겠습니다 . 그런 다음 확인을 누릅니다.

테이블의 공백을 클릭하면 아래에 중첩된 테이블의 미리보기가 표시됩니다. 이 중첩 테이블의 수량이 서로 상쇄되고 수량 잔액이 0임을 알 수 있습니다.

다음 중첩 테이블을 보면(따라서 다음 행의 공백에서 옆으로 클릭) 수량 열이 서로 상쇄되지 않고 수량 균형이 0보다 큰 것을 볼 수 있습니다.

나중에 중첩 테이블 변환에 사용할 수 있도록 사용자 지정 열을 추가해 보겠습니다. 따라서 맞춤 열 추가를 선택합니다 .

해당 열의 이름을 Transformations 로 바꾸고 자리 표시자로 0을 추가하겠습니다. 그런 다음 확인을 클릭합니다.

LuckyTemplates 파워 쿼리에서 중첩 테이블 변환

이제 이러한 중첩 테이블을 변환하기 위한 논리를 만들 준비가 되었습니다. 그러나 우리는 그것들을 많이 쓰고 싶지 않으며 그럴 필요도 없습니다.

우리는 대부분 사용자 인터페이스를 사용할 수 있습니다. 별도의 쿼리에서 이러한 변환에 대한 논리를 작성하는 경우 가장 정교한 시나리오는 3개 또는 4개의 행이 있는 시나리오였습니다.

이제 마지막 행의 오른쪽을 클릭하면 이 테이블에 4개의 행이 있는 것을 볼 수 있습니다. 따라서 이것을 사용하여 변환을 구축할 수 있습니다.

공백의 측면을 마우스 오른쪽 버튼으로 클릭하고 새 쿼리로 추가 를 선택합니다 .

이제 중첩 테이블이 확장되고 새 쿼리로 추가된 것을 볼 수 있으며 이를 사용하여 필요한 논리를 빌드할 수 있습니다.

이동 유형 및 게시 날짜 정렬

이제 가장 먼저 해야 할 일은 Movement Type 을 기준으로 정렬하는 것입니다 . 내림차순을 선택합시다 . 이렇게 하면 나가는 거래가 항상 맨 위에 있게 됩니다.

다음으로 Posting Date를 오름차순 으로 정렬하여 행을 생략하는 경우 항상 FIFO 원칙(선입선출)에 따라 수행되도록 합니다.

누적 합계 만들기

제거할 행을 식별하기 위해 누계를 추가하여 비활성화하고 해당 작업에 List.FirstN을 사용할 수 있습니다. 이 기능은 특정 번호 또는 기준에 따라 상위 항목이 유지되는 다른 목록을 기반으로 목록을 만듭니다.

쿼리로 돌아가 보겠습니다. List.FirstN에는 두 가지가 필요합니다. 먼저 해당 목록이 필요하며 해당 목록은 Quantity 열입니다. Quantity 열 머리글을 마우스 오른쪽 버튼으로 클릭하면 Add as NewQuery 옵션이 표시됩니다 .

여기에서 해당 목록을 생성하는 데 필요한 코드를 볼 수 있습니다. 적용된 단계에서 마지막 단계를 가리킨 다음 해당 괄호 안의 열을 식별하는 것을 볼 수 있습니다.

List.FirstN에 필요한 두 번째 부분은 해당 목록에서 유지할 숫자 수를 식별하는 숫자입니다. 이를 위해 인덱스를 사용할 수 있으므로 이 행에 대해 하나(1)의 인덱스 열을 추가하겠습니다.

따라서 숫자 1의 경우 수량 열의 첫 번째 행에 숫자를 유지하는 식으로 계속됩니다. 그리고 우리는 이제 그것을 요약할 수 있습니다.

맞춤 열을 추가하고 이름을 Running Total 로 지정하겠습니다 . 여기서 List.FirstN을 사용하고 첫 번째 매개변수가 해당 목록인 Quantity 열임을 알 수 있습니다. 우리는 마지막 단계를 가리켜야 하며, 그 마지막 단계는 Added Index 입니다 .

우리는 원하는 열을 식별했으며 이것이 Quantity 열입니다. 우리의 수는 인덱스 열에 있습니다.

이제 목록을 반환합니다. 흰색 공간 옆을 클릭하면 해당 목록의 내용을 볼 수 있습니다. 첫 번째 레코드의 경우 해당 목록의 맨 위 행만 유지했습니다. 두 번째 레코드의 경우 상위 두 행을 유지했습니다.

이제 우리가 해야 할 일은 이러한 수량을 합산하는 것입니다. 이를 위해 Lists.Sum을 사용할 수 있습니다. 그래서 수식 입력줄에 추가하겠습니다. 그리고 Type 도 넣겠습니다 .

조건에 따라 행 필터링

이 누적 합계를 통해 유지하려는 행을 식별할 수 있습니다. 0보다 큰 행만 유지하려고 하므로 필터 조건을 추가해 보겠습니다.

이제 도우미 열을 제거할 수 있습니다. 인덱스 및 누적 합계를 선택한 다음 열 제거를 클릭합니다.

이제 고급 편집기에서 생성한 코드를 복사할 수 있습니다. Advanced Editor 를 열면 이것이 분할 단계임을 알 수 있습니다. 따라서 그 아래의 모든 항목을 선택하고 복사할 수 있습니다.

이제 원래 쿼리로 돌아가 보겠습니다. 다시 고급 편집기를 엽니다. 자리 표시자 대신 새 줄로 이동해 보겠습니다. 변수의 중간 계산에서 값을 캡처할 수 있는 " let " 식을 사용하겠습니다 . 따라서 "let"하고 새 줄로 이동하여 내 변환을 붙여넣습니다. 또한 Type 을 선언하겠습니다 .

위의 강조 표시된 코드는 모든 행 열 내에 배치된 확장된 중첩 테이블을 가리킵니다. 따라서 이 강조 표시된 코드를 AllRows 로 대체하여 이를 지적하겠습니다 .

따라서 변환 열에는 이제 중첩 테이블이 있습니다. 마지막 행을 기반으로 쿼리를 작성합니다. 맞습니까? 그리고 우리가 시작했을 때 그것은 4개의 행을 포함했고 2개만 포함했습니다. 우리는 또한 그 양의 균형을 0으로 가지고 있습니다. 맞습니까? 이제 빈 테이블이 있습니다.

Quantity Balance 열에서 필터링하여 제거할 수 있습니다. 우리는 수량 균형이 0이 되는 것을 원하지 않습니다.

그런 다음 변환 열을 선택하고 다른 열을 제거하겠습니다. 이제 변환 중첩 테이블을 확장할 수 있습니다. 원래 열 이름을 접두어로 사용하지 말고 확인을 누르십시오.

변환 탭에서 데이터 유형을 선택하면 모두 완료됩니다. 이것이 트랜잭션 데이터를 정리하는 방법입니다.

LuckyTemplates의 Unpivot 및 Pivot 기본 사항 – 쿼리 편집기
LuckyTemplates Advanced Editor를 사용할 때의 연습 팁 검토
LuckyTemplates의 고급 변환

결론

이 자습서에서는 트랜잭션 데이터를 정리하는 방법, 특히 중복 행을 제거하는 방법을 설명했습니다. 이는 LuckyTemplates 파워 쿼리를 활용할 때 사용할 수 있는 훌륭한 기술입니다.

나는 당신이 이것을 즐겼기를 바랍니다. 자세한 내용은 아래의 전체 비디오 자습서를 시청하십시오. LuckyTemplates Power Query 편집기와 관련된 자세한 내용은 아래 링크를 확인하세요.

건배!

여자 이름