Как создать пользовательский столбец индекса в PowerQuery?

Question

У меня есть следующие данные в PowerQuery:

| ParentX | A |
| ParentY | A |
| ParentZ | A |
| ParentY | B |
| ParentZ | B |
| ParentX | C |
| ParentY | C |
| ParentZ | C |

Я хочу добавить столбец индекса, который подсчитывает количество родителей для элемента:

| ParentX | A | 3 |
| ParentY | A | 2 |
| ParentZ | A | 1 |
| ParentY | B | 2 |
| ParentZ | B | 1 |
| ParentX | C | 3 |
| ParentY | C | 2 |
| ParentZ | C | 1 |

Конечной целью является поворот на основе этого нового столбца, например:

| Object | Root    | Parent 2 | Parent 3 |
| A      | ParentZ | ParentY  | ParentX  |
| B      | ParentZ | ParentY  |          |
| C      | ParentZ | ParentY  | ParentX  |

Answer 1

Вот запрос, который я использовал для создания столбца индекса в вопросе:

let
    // This has the original parent/child column
    Source = #"Parent Child Query",

    // Count the number of parents per child
    #"Grouped Rows" = Table.Group(Source, {"Attribute:id"}, {{"Count", each Table.RowCount(_), type number}}),

    // Add a new column of lists with the indexes per child
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ParentIndex", each List.Numbers([Count], [Count], -1)),

    // Expand the lists in the previous step
    #"Expand ParentIndex" = Table.ExpandListColumn(#"Added Custom", "ParentIndex"),

    // Create the column name columns (Parent.1, Parent.2, etc)
    #"Added Custom1" = Table.AddColumn(#"Expand ParentIndex", "ParentColumn", each "Parent."&Text.From([ParentIndex])),

    // Adds an index column that you use when merging with the original table
    #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1)
in
    #"Added Index"

Как только это было сделано, я создал другой запрос для хранения объединенного результата:

let
    // This is the original parent/child column
    Source = #"Parent Child Query",

    // Add an index column that matches the index column in the previous query
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),

    // Merge the two queries based on the index columns
    Merge = Table.NestedJoin(#"Added Index",{"Index"},#"Epic Parent Indices",{"Index"},"NewColumn"),

    // Expand the new column
    #"Expand NewColumn" = Table.ExpandTableColumn(Merge, "NewColumn", {"ParentColumn"}, {"ParentColumn"}),

    // Remove the index column
    #"Removed Columns" = Table.RemoveColumns(#"Expand NewColumn",{"Index"}),

    // Sort the data by attribute and then by Parent column so the columns will be in the right order
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attribute:id", Order.Descending}, {"ParentColumn", Order.Ascending}}),

    // Pivot!
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[ParentColumn]), "ParentColumn","Parent:id")
in
    #"Pivoted Column"

Здесь было три ключевых шага:

1. Используйте Table.Group, чтобы получить количество родителей на дочерний элемент.
2. Используйте List.Numbers, чтобы получить значения индекса для каждого родителя / дочернего отношения.
3. Используйте Table.AddIndexColumn, чтобы добавить индексные столбцы, которые будут использоваться в качестве ключа при вызове Table.Join. Если вы этого не сделаете, в результате слияния вы получите повторяющиеся данные.

Answer 2

1. Создать таблицу Excel с 2 столбцами (Parents, Child)
2. Используйте эту таблицу в Power Query
3. Вставить функцию Combiner.CombineTextByDelimiter(";") (См. Line3)
4. Группа по Child и используйте функцию выше (см. строку 4)
5. Результат разделения (строка 5)

Код:

let
    Quelle    = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    fcombine  = Combiner.CombineTextByDelimiter(";"), 
    #"Group1" = Table.Group(Quelle, {"Child"}, {{"Parents", each fcombine([Parent]), type text}}),
    #"Split1" = Table.SplitColumn(#"Group1", "Parents", Splitter.SplitTextByDelimiter(";"),{"Parents.1", "Parents.2", "Parents.3"}),
    #"Result" = Table.TransformColumnTypes(#"Split1", {{"Parents.1", type text}, {"Parents.2", type text}, {"Parents.3", type text}})
in
    #"Result"

Привет Р.