Forum Discussion
Continued ContributorNeed a new id for my column
I have a table with an id like this:
| 69242C54-AC2D-EB11-A813-000D3A44A095 |
| 6B242C54-AC2D-EB11-A813-000D3A44A095 |
I need to create a new id from that id that equals a number if possible?
My issue is that the row sometimes is together with another row, meaning - row 1 and row 23 are two different people, but they live together and needs to be identified as one contact so to speak. From my table I can see the above id and in another column I can see the id of the row that matches (as in the above with row 1 and 23). So I know they are together but I need to count them as one. I am out of ideas 😞
Rather than try to identify the rows that are together and then try to add related fields to all rows, I'd recommend modeling these into two different table. Perhaps that is your intent behind creating a new ID?
Is it ever more than 2 people in a household? If yes, what does that look like within the data?
It sounds like when IDs are in the same household, they both reference the other household member in another "Other Person" column of some kind?
Please give a more complete example of what the table looks like. For now, I'm assuming something like the below:
SourceTable
Person ID Partner ID ABC-001 ABC-002 ABC-002 ABC-001 XYZ-001 GHI-002 LMN-005 null GHI-002 XYZ-001 DEF-010 null The following will construct a unifying Household column listing all people in the household (assuming up to two non-null IDs from two columns), then group on that column, add the new ID column, then expand out the columns from the previous grouping.
let Source = SourceTable, AddHouseholdCol = Table.AddColumn( Source, "Household", each List.Sort( { [Person ID], [Partner ID] } ), type {text} ), GroupOnHousehold = Table.Group( AddHouseholdCol, "Household", {"Cols", each Table.RemoveColumns( _, "Household" ), Value.Type( Source ) } ), RemoveHouseholdCol = Table.RemoveColumns( GroupOnHousehold, "Household" ), AddHouseholdID = Table.AddIndexColumn(RemoveHouseholdCol, "Household ID", 1 ), ExpandCols = Table.ExpandTableColumn( AddHouseholdID, "Cols", Table.ColumnNames( Source ) ) in ExpandColsOutput with above SourceTable:
2 Replies
- MarkLaf
Super User
Rather than try to identify the rows that are together and then try to add related fields to all rows, I'd recommend modeling these into two different table. Perhaps that is your intent behind creating a new ID?
Is it ever more than 2 people in a household? If yes, what does that look like within the data?
It sounds like when IDs are in the same household, they both reference the other household member in another "Other Person" column of some kind?
Please give a more complete example of what the table looks like. For now, I'm assuming something like the below:
SourceTable
Person ID Partner ID ABC-001 ABC-002 ABC-002 ABC-001 XYZ-001 GHI-002 LMN-005 null GHI-002 XYZ-001 DEF-010 null The following will construct a unifying Household column listing all people in the household (assuming up to two non-null IDs from two columns), then group on that column, add the new ID column, then expand out the columns from the previous grouping.
let Source = SourceTable, AddHouseholdCol = Table.AddColumn( Source, "Household", each List.Sort( { [Person ID], [Partner ID] } ), type {text} ), GroupOnHousehold = Table.Group( AddHouseholdCol, "Household", {"Cols", each Table.RemoveColumns( _, "Household" ), Value.Type( Source ) } ), RemoveHouseholdCol = Table.RemoveColumns( GroupOnHousehold, "Household" ), AddHouseholdID = Table.AddIndexColumn(RemoveHouseholdCol, "Household ID", 1 ), ExpandCols = Table.ExpandTableColumn( AddHouseholdID, "Cols", Table.ColumnNames( Source ) ) in ExpandColsOutput with above SourceTable:
- BeaBF
Bokazoit Hi! Try with this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrM0MjFyNjXRdXQ2ctF1dTI01HW0MDTWNTAwcDF2NDFxNLA0VYrVASp0IkZhLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),// Step 1: Assuming there is a column that links rows for grouping (e.g., "GroupID_Column").
// If you don't have such a column, this step should be adjusted based on your actual structure.
// Group rows by "GroupID_Column" (this should be the column that identifies which rows are related)
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {"Grouped Data", each _, type table [ID=nullable text]}),
// Step 2: Add an index column to uniquely identify each group
#"Add Group Index" = Table.AddColumn(#"Grouped Rows", "Group ID", each Table.AddIndexColumn([Grouped Data], "GroupIndex", 1, 1, Int64.Type)),
// Step 3: Expand the table to get the original data back
#"Expanded Grouped Data" = Table.ExpandTableColumn(#"Add Group Index", "Grouped Data", {"ID"}, {"ID"}),// Step 4: Expand the "Group ID" column to create a new unique ID column for each group
#"Expanded Group ID" = Table.ExpandTableColumn(#"Add Group Index", "Group ID", {"GroupIndex"}, {"Group ID"})in
#"Expanded Group ID"BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
