Combine Several Columns Fields to List and Count Distinct

[Anonymous]

Let me start by describing the final output. I have values in column A, B, C, D. I need to determine the if there are duplicates in A2, B2, C2, D2.

I've searched around since the morning and every "list" function always lists the entire column. I don't want the entire column as list. I want a list with values A2, B2, C2, and D2. From there, I'm guessing I would have to group and then count the output. I can't seem to figure out how to send the date values in the four columns to a list and then select, List.Distinct.

[Anonymous · Accepted solution]

In Power Query:

Select ID Column --> Unpivot Other Columns

Remove Attribute

GroupBy ID and Count Disticnt Rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1VdJRMtY3AiIDQ0s0tqm+oQGMbaZvaAphx+pEK5mbmwPFTPRNYdLYmRb6llA9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date1 = _t, Date2 = _t, Date3 = _t, Date4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"ID"}, {{"Distcin Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"

[Nolock · Accepted solution]

Hi @Anonymous,

there is also another solution. You can create a table of cells of a row (each Table.FromRecords({[Dt = [Date1]], [Dt = [Date2]], [Dt = [Date3]]})), then apply Distict and RowCount.

The example contains also some test data (3 columns Date1, Date2, and Date3):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMLRU0lEy0jOCMY31jCHMWJ1oJRM9E5i4qZ4pBhOkxEzPDCaOxDTXM4cqiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t, Date3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "AllDatesAsTable", each Table.FromRecords({[Dt = [Date1]], [Dt = [Date2]], [Dt = [Date3]]})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DistinctDatesAsTable", each Table.Distinct([AllDatesAsTable], "Dt")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "DistinctCount", each Table.RowCount([DistinctDatesAsTable]))
in
    #"Added Custom2"

[Anonymous · Accepted solution]

In Power Query:

Select ID Column --> Unpivot Other Columns

Remove Attribute

GroupBy ID and Count Disticnt Rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1VdJRMtY3AiIDQ0s0tqm+oQGMbaZvaAphx+pEK5mbmwPFTPRNYdLYmRb6llA9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date1 = _t, Date2 = _t, Date3 = _t, Date4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"ID"}, {{"Distcin Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"