Need a new id for my column

[Bokazoit]

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 😞

[MarkLaf · Accepted solution]

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
    ExpandCols

Output 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!