powerquery - excel 파워 쿼리 - 그룹화 및 조옮김

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

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"

첫 번째 그룹화 StoreID 과 ProductID @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가 지적했듯이 확장은 동적으로 만들어야하므로 확장 할 열 수를 결정하기 위해 사용자 지정 그룹에 열을 추가했습니다. 이 숫자는 한 상점의 최대 제품 수이며 열 이름 목록을 생성하는 데 사용됩니다.

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

기본적으로 ..

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"