Duplicate Rows Based On Cell Value In different Column

Hi Technowolf ,

We can meet your requirement easier in the Power Query Editor than Dax way:

1. Create a custom column use following formula:

let 
    SN = [#"SN With Non-Duplicates"] 
in 
    if [Duplicate Check] = "Duplicate" 
        and SN <> 0 
        and SN <> 1 
    then List.Repeat({SN},SN)
    else {SN}

2. After get the column contain list, we expand it to the new rows

3. Delete the old SN columns then rename the custom as the origin name.

4. Then we can get the result table

We suggest you to delete the the screenshot you have shared if it contain any confidential information or it come from real data. If you still want the DAX solution, just tell us and we will try to make it.

All the M Query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS/TsQwDIffpXOR4iRN2xEo3HYvUHVApxuQENwA70/SxK6dPxyDvbRf7Xz5qevaPXZ9pydsoH1zqcDX+evzYfm5fbxf3r6v+ILutr4gjW9jKlC+cWyU1EyUTXMAv82puUKFBsP+IBbAf6lwJpPKZdDUgsLmNtV+RmGjRYXPDakKGbZFzUy+zUe1ZmnFxOcGjYCMIoicV6C5hCKJynVtP9WiULlJ4+7sFyFUbjEkRRCPeU8SRe8YkvzC1A49Swi1uyQy9wE1CK2PmMl8SUvkEiAgcuKxL1J1WDkwoKvD3NekQIOMS5PwP3W+SBL4TeRRduTzVQDA7mDKmGFfo2T4r2dsHa4Gij9PLStOgpofi29bTCTuRJxmycKNOaOjke0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, #"Case ld" = _t, #"Case Created Date" = _t, #"Case Closed Date" = _t, #"Product Series Name" = _t, #"# of Cases" = _t, #"Index Ranking" = _t, #"Duplicate Check" = _t, #"# Cases Per SN" = _t, #"SN With Non-Duplicates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Case ld", Int64.Type}, {"Case Created Date", Int64.Type}, {"Case Closed Date", Int64.Type}, {"Product Series Name", Int64.Type}, {"# of Cases", Int64.Type}, {"Index Ranking", Int64.Type}, {"Duplicate Check", type text}, {"# Cases Per SN", Int64.Type}, {"SN With Non-Duplicates", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
    SN = [#"SN With Non-Duplicates"] 
in 
    if [Duplicate Check] = "Duplicate" 
        and SN <> 0 
        and SN <> 1 
    then List.Repeat({SN},SN)
    else {SN}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"SN With Non-Duplicates"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "SN With Non-Duplicates"}})
in
    #"Renamed Columns"

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Technowolf ,

We can create the list contain the case id that they need to compare, just need to use the columns of [Serial Number] , [Duplicate Check] and the [Case ld]

let 
    SerialNumber = [Serial Number],
    SN_Table = Table.SelectRows(
        #"Changed Type",
        each [Serial Number] = SerialNumber 
            and [Duplicate Check]="Non-Duplicate"
        ),
    n = Table.RowCount(SN_Table),
    SerialNumberListOfNonD = 
        Table.ToList(
            Table.TransformColumnTypes(
                Table.SelectColumns(SN_Table,"Case ld"),
                {{"Case ld", type text}}
            )
        )
in 
    if [Duplicate Check] = "Duplicate" 
        and n > 1
    then SerialNumberListOfNonD
    else {"No-Case-To-Compare"}

then expand the list as previous reply.

All the M Query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS/TsQwDIffpXOR4iRN2xEo3HYvUHVApxuQENwA70/SxK6dPxyDvbRf7Xz5qevaPXZ9pydsoH1zqcDX+evzYfm5fbxf3r6v+ILutr4gjW9jKlC+cWyU1EyUTXMAv82puUKFBsP+IBbAf6lwJpPKZdDUgsLmNtV+RmGjRYXPDakKGbZFzUy+zUe1ZmnFxOcGjYCMIoicV6C5hCKJynVtP9WiULlJ4+7sFyFUbjEkRRCPeU8SRe8YkvzC1A49Swi1uyQy9wE1CK2PmMl8SUvkEiAgcuKxL1J1WDkwoKvD3NekQIOMS5PwP3W+SBL4TeRRduTzVQDA7mDKmGFfo2T4r2dsHa4Gij9PLStOgpofi29bTCTuRJxmycKNOaOjke0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, #"Case ld" = _t, #"Case Created Date" = _t, #"Case Closed Date" = _t, #"Product Series Name" = _t, #"# of Cases" = _t, #"Index Ranking" = _t, #"Duplicate Check" = _t, #"# Cases Per SN" = _t, #"SN With Non-Duplicates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Case ld", Int64.Type}, {"Case Created Date", Int64.Type}, {"Case Closed Date", Int64.Type}, {"Product Series Name", Int64.Type}, {"# of Cases", Int64.Type}, {"Index Ranking", Int64.Type}, {"Duplicate Check", type text}, {"# Cases Per SN", Int64.Type}, {"SN With Non-Duplicates", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
    SerialNumber = [Serial Number],
    SN_Table = Table.SelectRows(
        #"Changed Type",
        each [Serial Number] = SerialNumber 
            and [Duplicate Check]="Non-Duplicate"
        ),
    n = Table.RowCount(SN_Table),
    SerialNumberListOfNonD = 
        Table.ToList(
            Table.TransformColumnTypes(
                Table.SelectColumns(SN_Table,"Case ld"),
                {{"Case ld", type text}}
            )
        )
in 
    if [Duplicate Check] = "Duplicate" 
        and n > 1
    then SerialNumberListOfNonD
    else {"No-Case-To-Compare"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Please let us know if the [Duplicate Check] column is also created from DAX formula.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Technowolf ,

After creating the "Case-To-Compare" column in the power query editor, we can create a calculate colmun use dax to comprae the valid status:

Condition 1 = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareClosed =
        LOOKUPVALUE ( 'Table'[Case Closed Date], 'Table'[Case Id], comareid )
    RETURN
        IF ( [Case Created Date] <= compareClosed, "TRUE", "FALSE" )
)

Condition 2 = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareCreated =
        LOOKUPVALUE ( 'Table'[Case Created Date], 'Table'[Case Id], comareid )
    RETURN
        IF ( [Case Closed Date] >= compareCreated, "TRUE", "FALSE" )
)

Pre_Valid_Status_2 =
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    IF (
        AND ( [Condition 1] = "TRUE", [Condition 2] = "TRUE" ),
        "Valid Duplicate Created",
        "Invalid Duplicate Created"
    )
)

Or Merge them into one

Pre_Valid_Status = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareCreated =
        LOOKUPVALUE ( 'Table'[Case Created Date], 'Table'[Case Id], comareid )
    VAR compareClosed =
        LOOKUPVALUE ( 'Table'[Case Closed Date], 'Table'[Case Id], comareid )
    RETURN
        IF (
            AND (
                [Case Created Date] <= compareClosed,
                [Case Closed Date] >= compareCreated
            ),
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

If you want to the logical that in same duplicate case, there are at least one valid and end restult is valid, we can create a calculated column use following formula:

End_Valid_Status = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR SerialNumber = [Serial Number]
    VAR CaseId = [Case Id]
    VAR t =
        FILTER ( 'Table', [Serial Number] = SerialNumber && [Case Id] = CaseId )
    RETURN
        IF (
            COUNTROWS ( FILTER ( t, [Pre_Valid_Status] = "Valid Duplicate Created" ) ) > 0,
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Technowolf ,

We can try to change the DAX formula to following: 

Pre_Valid_Status =
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    IF ( [Duplicate Check] = "Duplicate", "Invalid Duplicate Created", BLANK () ),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareCreated =
        LOOKUPVALUE ( 'Table'[Case Created Date], 'Table'[Case Id], comareid )
    VAR compareClosed =
        LOOKUPVALUE ( 'Table'[Case Closed Date], 'Table'[Case Id], comareid )
    RETURN
        IF (
            AND (
                [Case Created Date] <= compareClosed,
                [Case Closed Date] >= compareCreated
            ),
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

End_Valid_Status =
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    IF ( [Duplicate Check] = "Duplicate", "Invalid Duplicate Created", BLANK () ),
    VAR SerialNumber = [Serial Number]
    VAR CaseId = [Case Id]
    VAR t =
        FILTER ( 'Table', [Serial Number] = SerialNumber && [Case Id] = CaseId )
    RETURN
        IF (
            COUNTROWS ( FILTER ( t, [Pre_Valid_Status] = "Valid Duplicate Created" ) ) > 0,
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

Best regards,