Marke duplicated base on other column

[HarryPotter115]

HI, 

Please help me in Power Querry.

This is sample data

The Data has two column A and B, I wand to add column C with criterial below:
1. It is 1 if value in A column is unique
2. It is 2 if value in A column is duplicated but value in B column is the same

3. It is 3 if value in A column is duplicated but value in B column has more than 2 value

[mussaenda]

Hi @HarryPotter115 

[jgeddes]

Here is an example of one way to accomplish this...

let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WclTSUTJSitWBsIzRWE5AlikayxnIMgSzXIAsEzgLKBsLAA==", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Criteria1 = _t, Criteria2 = _t]
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {{"Criteria1", type text}, {"Criteria2", type text}}
    ),
    Custom1 = 
    Table.AddColumn(
        #"Changed Type", 
        "_mark", 
        each 
        if List.Count(Table.SelectRows(#"Changed Type", (x)=> x[Criteria1] = [Criteria1])[Criteria1]) = 1
            then 1
            else if List.Count(Table.SelectRows(#"Changed Type", (x)=> x[Criteria1] = [Criteria1])[Criteria1]) = 2 and List.Count(Table.SelectRows(#"Changed Type", (x)=> x[Criteria2] = [Criteria2] and x[Criteria1] = [Criteria1])[Criteria2]) = 2
                then 2
                else if List.Count(Table.SelectRows(#"Changed Type", (x)=> x[Criteria1] = [Criteria1])[Criteria1]) = 2 and List.Count(Table.SelectRows(#"Changed Type", (x)=> x[Criteria2] = [Criteria2] and x[Criteria1] = [Criteria1])[Criteria2]) <> 2
                    then 8
                    else if List.Count(Table.SelectRows(#"Changed Type", (x)=> x[Criteria1] = [Criteria1])[Criteria1]) > 2
                then 3
                else 9
    )
in
    Custom1

[HarryPotter115]

can u send file excel with power querry link for me to deeply understand your solution. I am a newbie in Powerqueery so I am not familar with M code of power querry

Thanks in advance

[jgeddes]

I can't upload anything but I will try and explain in a bit more detail.

The first step was to create a sample table with the data in your example.
I entered the data by clicking on the 'Enter Data' icon on the ribbon

and entered the data...

that generates the code...

Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WclTSUTJSitWBsIzRWE5AlikayxnIMgSzXIAsEzgLKBsLAA==", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Criteria1 = _t, Criteria2 = _t]
    )

the next step ensures both columns are text. (Without me knowing for sure what your end data is, I use text as it is easier to manipulate)

#"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {{"Criteria1", type text}, {"Criteria2", type text}}
    )

Now comes the bulk of the transformation.

The basic idea is, that I take the criteria you set and I create a table within step and then count the results of in that table in order to compare to the set criteria.

So for Mark = 1 you said that 'Criteria1' could only appear once.

That is represented by the code:

if List.Count(Table.SelectRows(#"Changed Type", (x)=> x[Criteria1] = [Criteria1])[Criteria1]) = 1

then 1

The blue text is selecting rows from our example table, returning only the rows that have the value that is in the current row in column 'Criteria1'.

The table is the previous step (#"Changed Type")

The current row value in 'Criteria1' is 'x[Criteria1]

The column we want to compare in is [Criteria1]

The (x)=> declares that this is function. (The 'x' in the (x) could be any character. You would just need to use that character in subsquent code. E.g., (c)=> c[Criteria1] = [Criteria])

Now that we have a table that only contains values from [Criteria1] that equal the value in the current row in [Criteria1] we want to count how many rows there are in that table (how many instances of the current value in [Criteria1].)
I use the List.Count() function to do this. The basic format to declare a list from a table is:

TableName[ColumnName]

The blue text created the table I needed and the green [Criteria1] on the end turns the table into a list of elements.

The List.Count(Table.SelectRows(#"Changed Type", (x)=> x[Criteria1] = [Criteria1])[Criteria1]) gives me the count and if that count equals 1 meaning the Criteria1 value in the current row only appears once. Then I return 1 as indicated in your initial criteria.

The rest of the 'Custom1' code is the series of if then statements that align with the criteria you set out. I use the same principle of counting instances of elements using List.Count. I just change the way the Table in the List.Count function is generated.

E.g., 

Table.SelectRows(#"Changed Type", (x)=> x[Criteria2] = [Criteria2] and x[Criteria1] = [Criteria1])[Criteria2])

selects all the rows where the Criteria2 column that match the current row Criteria2 value and Criteria1 column that match the current row Criteria1 value.

Hope this helps.
There are a ton of List functions where this principle is useful. Things like List.Sum, List.Max etc.