Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HarryPotter115
New Member

Marke duplicated base on other column

HI, 

Please help me in Power Querry.

This is sample data

HarryPotter115_0-1696259314112.png

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

4 REPLIES 4
mussaenda
Super User
Super User

Hi @HarryPotter115 

mussaenda_0-1696346409946.png

 

jgeddes
Super User
Super User

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

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

jgeddes_0-1696340862660.png

and entered the data...

jgeddes_1-1696340888019.png

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors