☰

Excel 표를 여러 중복 행의 고유한 열로 어떻게 바꾸나요? (바람직하게는 PowerQuery에서)

tag-icon • tag-icon microsoft-excel power-query
Excel 표를 여러 중복 행의 고유한 열로 어떻게 바꾸나요? (바람직하게는 PowerQuery에서)

나는 모든 곳을 살펴보고 많은 것을 시도했지만 대부분의 사람들은 이 문제를 해결할 필요가 없는 것 같습니다. :-( 두 개의 열이 있는 테이블이 있습니다. 열 A(TAG)에는 해당 패턴이 반복적으로 반복되는 4개의 고유한 값이 있습니다. 이는 내 고유한 열 이름이 됩니다. 열 B(VALUE)에는 해당 태그와 일치하는 값이 있습니다.

예제 테이블:
소스 테이블

이제 다음과 같은 테이블을 만들고 싶습니다. (TAG 행의 각 고유 값은 열 헤더가 되고 VALUE 열의 모든 값은 새 열 아래의 개별 값이 됩니다.)

원하는 대상 테이블:
원하는 대상 테이블

데이터가 가져온 CSV를 가져올 때 이미 PowerQuery에서 이 모든 전치 작업을 수행하는 것이 좋지만 나중에 일부 범위/테이블 작업에서도 이 작업을 수행해도 괜찮습니다.

어떤 도움이라도 정말 감사하겠습니다 :-)

답변1

파워 쿼리에서는 VALUE 열을 값으로 사용하여 TAG 열을 피벗합니다 no aggregation.

그러나 데이터 세트에서 이 작업을 수행하면 동일한 피벗 값에 대한 항목이 여러 개 있으므로 오류 값이 반환됩니다.

각 개별 태그 항목에 순차적으로 번호를 매기는 색인 열을 추가하면 이를 방지할 수 있습니다.

이를 수행하고 TAG가 항상 동일한 순서로 존재하지 않도록 하려면 다음을 수행하십시오.

  • 태그별로 그룹화
  • 각 하위 테이블에 인덱스 열 추가
  • 테이블 확장
  • 집계 없이 TAG에서 피벗
  • 인덱스 열 제거

아래 코드에서 주석과 적용 단계를 검토하여 더 잘 이해하세요.

데이터 세트에서 이 방법이 너무 느린 경우 더 빠르게 실행될 수 있는 사용자 정의 함수가 게시되어 있습니다.

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//Group By Tage
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TAG"}, {

        //add an Index column to each subtable
        {"with Index", each Table.AddIndexColumn(_,"Index",0,1), type table [TAG=nullable text, VALUE=nullable text, Index=Int64.Type]}}),

//Expand the sub tables
    #"Expanded with Index" = Table.ExpandTableColumn(#"Grouped Rows", "with Index", {"VALUE", "Index"}),

//Pivot with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Expanded with Index", List.Distinct(#"Expanded with Index"[TAG]), "TAG", "VALUE"),

//Remove the Index column
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

여기에 이미지 설명을 입력하세요

비집계 피벗 실행을 위한 사용자 정의 함수를 표시하도록 편집되었습니다.

사용자 정의 기능
빈 쿼리에 붙여넣고 코드 주석과 같이 이름을 바꿉니다.

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

메인코드

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//call custom pivot function
    pivot = fnPivotAll(#"Changed Type","TAG","VALUE")
in
    pivot

관련 정보