>source

데이터 테이블을 새로 고칠 때 지속적으로 새로운 storedId와 newproductID가있는 SQL에이 테이블이 있습니다.

StoreID   ProductID  Quantity
1           A          4
1           B          5
1           B          9
2           B          3
2           C          4       
.
.                    
7           F          2

내 StoreID를 함께 그룹화하고 아래 표와 같이 수량 합계로 제품 ID를 바꾸고 싶습니다. 각 상점에는 0 ~ 30 개의 제품이있을 수 있습니다. 따라서 30 개의 제품이있는 경우 "productID"열 30 개와 "SumQuantity"열 30 개가 필요합니다.

StoredId   ProductId1   ProductID2  ...   SumQuantity1   SumQuantity2 ...
1              A            B                4               14
2              B            C                3                4
.
.
7              F                             2
.
.

파워 쿼리 Excel에서 어떻게 할 수 있습니까?


  • 답변 # 1

    왜 그렇게 끔찍한 형식의 데이터를 원하는지 잘 모르겠지만 여기에 내가 접근하는 방법이 있습니다.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhOlWB0IzwmITVF4lnCeMxAbGsG5LiCuMZhrBFWL4DnDTTUHstyAGKgxFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StoreID = _t, ProductID = _t, Quantity = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"StoreID", Int64.Type}, {"ProductID", type text}, {"Quantity", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"StoreID", "ProductID"}, {{"SumQuantity", each List.Sum([Quantity]), type nullable number}}),
        #"Custom Grouping" = Table.Group(#"Grouped Rows", {"StoreID"},
            {
                {"Products", each Table.FromRows({[ProductID]},List.Transform({1..List.Count([ProductID])}, each "ProductID" & Number.ToText(_))), type table},
                {"Quantities", each Table.FromRows({[SumQuantity]},List.Transform({1..List.Count([SumQuantity])}, each "SumQuantity" & Number.ToText(_))), type table},
                {"Count", Table.RowCount, Int64.Type}
            }
        ),
        ColumnsToExpand = List.Max(#"Custom Grouping"[Count]),
        ProductColumns = List.Transform({1..ColumnsToExpand}, each "ProductID" & Number.ToText(_)),
        QuantityColumns = List.Transform({1..ColumnsToExpand}, each "SumQuantity" & Number.ToText(_)),
        #"Expanded Products" = Table.ExpandTableColumn(#"Custom Grouping", "Products", ProductColumns, ProductColumns),
        #"Expanded Quantities" = Table.ExpandTableColumn(#"Expanded Products", "Quantities", QuantityColumns, QuantityColumns)
    in
        #"Expanded Quantities"
    
    

    첫 번째 그룹화 StoreIDProductID @horseyride가 제안한 것과 동일하지만 거기에서 다른 길을갑니다.

    다음 단계는 StoreID 각 상점에 대해 원하는 테이블을 구성하십시오. 이 단계 결과는 편집기에서 다음과 유사합니다 (하단의 미리보기는 선택한 셀에 포함 된 내용입니다).

    "제품"에 대해이 테이블이 어떻게 구성되는지 살펴 보겠습니다.

    Table.FromRows(
        {[ProductID]},
        List.Transform(
            {1..List.Count([ProductID])},
            each "ProductID" & Number.ToText(_)
        )
    )
    
    

    이 목록 [ProductID] 현재 상점과 연관된 ID 목록입니다. 선택한 셀의 경우 {"A","B"} . 두 개의 값이 있으므로 List.Count 따라서 위의 내용은 다음과 같이 단순화됩니다.

    Table.FromRows(
        {{"A", "B"}},
        List.Transform(
            {1,2},
            each "ProductID" & Number.ToText(_)
        )
    )
    
    

    목록 변환 후 이것은 단순히

    Table.FromRows({{"A", "B"}}, {"ProductID1", "ProductID2"})
    
    

    위 스크린 샷의 미리보기처럼 보입니다.

    수량 데이터의 구성은 유사합니다. 해당 열이 정의 된 후 남은 것은 다음 두 열을 모두 확장하는 것입니다.

    <시간 />

    편집하다:

    @horseyrides가 지적했듯이 확장은 동적으로 만들어야하므로 확장 할 열 수를 결정하기 위해 사용자 지정 그룹에 열을 추가했습니다. 이 숫자는 한 상점의 최대 제품 수이며 열 이름 목록을 생성하는 데 사용됩니다.

  • 답변 # 2

    사용자 지정 코딩 없이는이를 수행하기가 어렵습니다.

    기본적으로 ..

    StoreID 및 ProductID로 그룹화 한 다음 수량 결합

    StoreID에서 다시 그룹화 한 다음 ProductID 및 Quantity에 대한 행을 세미콜론 구분 기호로 결합합니다.

    구분 기호에서 열을 분할합니다. 사전에 필요한 열 수를 모르기 때문에 4 줄의 사용자 지정 코드가 필요합니다.

    위의 단계에서 필요한 형식 인 텍스트의 숫자를 숫자로 변환합니다.

    견본:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // group and sum quantities
    #"Grouped Rows" = Table.Group(Source, {"StoreID", "ProductID"}, {{"Quantity", each List.Sum([Quantity]), type number}}),
    // group second time and combine ProductID and Quantity from different rows onto a single row with ; as delimiter
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"StoreID"}, {{"ProductID", each Text.Combine(List.Transform([ProductID], each Text.From(_)), ";"), type text},{"Quantity", each Text.Combine(List.Transform([Quantity], each Text.From(_)), ";"), type text}}),
    //Dynamically split the two delimited columns into any number of columns 
    DynamicColumnList  = List.Transform({1 ..List.Max(Table.AddColumn(#"Grouped Rows1","Custom", each List.Count(Text.PositionOfAny([ProductID],{";"},Occurrence.All)))[Custom])+1}, each "ProductID." & Text.From(_)),
    DynamicColumnList2 = List.Transform({1 ..List.Max(Table.AddColumn(#"Grouped Rows1","Custom", each List.Count(Text.PositionOfAny([Quantity],{";"},Occurrence.All)))[Custom])+1}, each "Quantity." & Text.From(_)),
    #"Split Column by Delimiter" =  Table.SplitColumn( #"Grouped Rows1", "ProductID",  Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), DynamicColumnList),
    #"Split Column by Delimiter2" = Table.SplitColumn( #"Split Column by Delimiter" , "Quantity", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), DynamicColumnList2),
    // convert numerical to numbers   
    #"ConvertToNumbers" = Table.TransformColumnTypes (#"Split Column by Delimiter2", List.Transform ( List.Difference(Table.ColumnNames(#"Split Column by Delimiter2"),Table.ColumnNames(#"Split Column by Delimiter")),each {_,type number}))
    in #"ConvertToNumbers"
    
    

  • 이전 kotlin - 기능적인 방식으로 목록의 모든 요소 결합
  • 다음 reshape - R에서 열 이름을 분할하고 이름의 일부를 삭제하고 데이터를 넓은 형식에서 긴 형식으로 변환하는 방법