Columns I exclude from Group by in power query disappear from my table

[Anonymous]

Hi,

I have a table that has multiple rows that match on a specific column. I went through a process to assign a parent_identifier as a custom column to all the rows so that I could create a parent table that relates one to many records. 

The process

1. I did a group by party_name and selected all rows
2. I added an index column
3. I then expanded the rows

This achieved an identifier which is the same for each row where it matches on party_name - perfect!

The problem

All of my other columns have disappeared and I need them.

If I add those columns into the group by, then they don't disappear however, I don't want to group by them because the other columns are what make them unique and I only want to group by party_name.

How do I group by one column and not lose the other columns?

This code below is configured with my preferred grouping of name but also includes _party_category because it ensures it sticks around at the end of the process but also, doesn't impact the result. All the other columns would impact the result.

let
    Source = #"Vendor - TWL and WSL",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"company_type_code", "supplier_code", "_party_role_category", "_party_role_type", "_party_category", "name"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Removed Other Columns",{{"name", Text.Proper, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Capitalized Each Word",{{"name", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","(","",Replacer.ReplaceText,{"name"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"name"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value1",{{"name", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"_party_category", "name"}, {{"Count", each _, type table [company_type_code=nullable text, supplier_code=nullable text, _party_role_category=text, _party_role_type=text, _party_category=text, name=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 5000, 1, Int64.Type),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"supplier_code", "_party_category", "name"}, {"Count.supplier_code", "Count._party_category", "Count.name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Count",{{"Count.supplier_code", "child_identifier"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"_party_category", "name", "Count._party_category", "Count.name", "Index", "child_identifier"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Index", "parent_identifier"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Count._party_category", "Count.name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([child_identifier] = "B239" or [child_identifier] = "C393"))
in
    #"Filtered Rows"

[HotChilli · Accepted solution]

A data sample would help.

On the Table.Group line I see that all the columns are included as an aggregation but on the ExpandTableColumn line only 3 are returned. Can you edit that step (using the interface) to return all columns.

I'm slightly confused that you say "All of my other columns have disappeared" . Is it returning the 3 columns as in th e code above? 

[Anonymous]

ah! That was it! I have no idea why it decided to omit any columns at all, thank you for your help, that's worked!

[Anonymous]

Just to add to this, I added aggregation columns into my group by configuration for the ones I wanted but, they lose their uniqueness - In my example record, reason why I have two records is because the company_type_code is different for each row. When I aggregate using MIN it uses first value for both, MAX uses last value for both.

So I didn't have to create an aggreated column for child_identifier but that didn't disappear, that has a value per row, how do I get it to treat the other 3 columns the same as that one?