Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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"
Solved! Go to 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?
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?
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!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |