How do I create a custom index column in PowerQuery?

Question

I have the following data in PowerQuery:

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

I want to add an index column that counts the number of parents for an element:

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

The end goal is to pivot based on this new column like this:

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

Vinay · Accepted Answer · 2015-04-29 17:12:54Z

Here's the query I used to generate the index column in the question:

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"

Once this was done I created another query to hold the merged result:

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"

There were three key steps here:

Use Table.Group to get the number of parents per child element.
Use List.Numbers to get index values for each parent/child relationship.
Use Table.AddIndexColumn to add index columns to be used as the key in the call to Table.Join If you don't do this then you'll get duplicate data in the merge.

juliocesar · Accepted Answer · 2016-04-04 03:15:00Z

0

Create an Excel Table with 2 Columns (Parents, Child)
Use this Table in Power Query
Insert function Combiner.CombineTextByDelimiter(";") (See Line3)
Group by Child and use function above (See Line 4)
Split result (Line 5)

The code:

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"

Greetings R.

edited Apr 4, 2016 at 3:15

juliocesar

5,8968 gold badges46 silver badges64 bronze badges

answered Apr 29, 2015 at 13:51

user4846728

1

1 Comment

Vinay Over a year ago

What do you mean by "insert function Combiner.CombineTextByDelimiter(";")"? I can't use CombineTextByDelimiter with Group By. Also, will this work for an arbitrary number of parents? that's what I have -- I use a max of three in the example but my data has more.

Collectives™ on Stack Overflow

How do I create a custom index column in PowerQuery?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related