Forum Discussion

KH_Mike's avatar
KH_Mike
Icon for Helper III rankHelper III
2 years ago

Create new column to check the duplication with certain condition

Hello All,   In Power Query, is it possible to create a new column based on below condition? There are "Data Group" either "Revenue" or "Cost". If the "Data Code" under "Data Group" is unique, the...
Need Help
spinfuzer's avatar
spinfuzer
Icon for Solution Sage rankSolution Sage
2 years ago

This is not exactly what you asked for, but I think it achieves what you are probably looking to do.  

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZI9C8IwEIb/imTuYNIPXfPVZnKodJDSQUo3qaCtv98DbYp4kEuX90Lg4Tm4t22Z5CJVWV6whNXDaxjnYX3tZD/N1xt8SCkhbVND8j3EhXUJFVZKeTglw/3jQ2utIZuzgcyiaWOMp/PYxa21G+BFXZalp7kojpHyqqo24YveObee7JAKGKcIPYL/6fX9OX1HTFVQLFySX4xeD5wLFwNdM1wJXEcpAyqk1ABXUgqAKvHTd28=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Data Group" = _t, #"Data Type" = _t, #"Data Code" = _t, Currency = _t, Amount = _t, Checking = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Data Group", type text}, {"Data Type", type text}, {"Data Code", type text}, {"Currency", type text}, {"Amount", Int64.Type}, {"Checking", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Checking"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Data Type", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Data Type.1", "Data Type.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data Type.1", type text}, {"Data Type.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"Data Type.2"]), "Data Type.2", "Amount", List.Sum),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each if List.NonNullCount({[Actual],[Accrual]}) = 2 then "Duplication" else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Actual] ?? [Accrual])
in
    #"Added Custom1"

 

KH_Mike's avatar
KH_Mike
Icon for Helper III rankHelper III
2 years ago

Hi spinfuzer ,

 

I got your point while I try to do it in my actual data but it take long time to perform this task. I guess maybe the data size too big (more than 30 millions rows of records). Any ideas how to speed up ? Thank you.

  • spinfuzer's avatar
    spinfuzer
    Icon for Solution Sage rankSolution Sage
    2 years ago

    This REQUIRES that the data is sorted exactly how it is shown in your example.  It also assumes Accrual always sorts before Actual as well.  That should be the case if it is in alphabetical order.  All this does it shift the table up one row and compares the current row values to the next row's values.

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZI9C8IwEIb/imTuYNIPXfPVZnKodJDSQUo3qaCtv98DbYp4kEuX90Lg4Tm4t22Z5CJVWV6whNXDaxjnYX3tZD/N1xt8SCkhbVND8j3EhXUJFVZKeTglw/3jQ2utIZuzgcyiaWOMp/PYxa21G+BFXZalp7kojpHyqqo24YveObee7JAKGKcIPYL/6fX9OX1HTFVQLFySX4xeD5wLFwNdM1wJXEcpAyqk1ABXUgqAKvHTd28=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Data Group" = _t, #"Data Type" = _t, #"Data Code" = _t, Currency = _t, Amount = _t, Checking = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Data Group", type text}, {"Data Type", type text}, {"Data Code", type text}, {"Currency", type text}, {"Amount", Int64.Type}, {"Checking", type text}}),
    prior_step = Table.RemoveColumns(#"Changed Type",{"Checking"}),
    tbl = Table.SelectColumns(prior_step, {"ID","Data Group","Data Type", "Data Code"}),
    temp_columns = Table.FromColumns(
        Table.ToColumns(prior_step) & Table.ToColumns(Table.RemoveFirstN(tbl,1)),
        Table.ColumnNames(prior_step) & Table.ColumnNames(Table.PrefixColumns(tbl,"temp"))
        ),
    check_column = Table.AddColumn(temp_columns, "Check", each if [Data Code] <> [temp.Data Code] then "Y" else if [ID] = [temp.ID] and [Data Group] = [temp.Data Group] then "N" else "Y"),
    remove_temp_columns = Table.RemoveColumns(check_column, {"temp.ID","temp.Data Group","temp.Data Type","temp.Data Code"}) 
in
    remove_temp_columns