Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Change Duplicate Rows to Zero/Null - NOT Delete

Hi all,   I'm hoping for a way to change duplicate rows to zero/null in Power Query/Dax . I have data which looks like the following:     I need to have a unique value rather than duplicat...
  • Ahmedx's avatar
    3 years ago

    see attached
    https://1drv.ms/u/s!AiUZ0Ws7G26RhhxoO5MJGPHBG1zL?e=I6lIMB

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMUzAyVtBVcMpMrkzOSVXSgQoBGQih8IzU1BwgbWQAJEwMlGJ1iNUZnJpYAqQMSdbonJGYmQekTREa3VKT0DVChLC6FWylsQUJOqFuNSVVH8yphjCdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Date = _t, Category = _t, Type = _t, #"Type Demand" = _t, #"Category Demand" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", type text}, {"Date", type date}, {"Category", type text}, {"Type", type text}, {"Type Demand", Int64.Type}, {"Category Demand", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Index", "Date", "Category Demand"}, {{"Count", each Table.AddColumn(Table.AddIndexColumn(_, "Index_2", 1,1), "Category_Demand", each if [Index_2] > 1 then 0 else [Category Demand]), type table }}),
        #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
        #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Index", "Date", "Category", "Type", "Type Demand", "Category_Demand"}, {"Index", "Date", "Category", "Type", "Type Demand", "Category_Demand"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Category_Demand", type number}})
    in
        #"Changed Type1"exColumn(_, "Index", 1,1), "Score_2", each if [Index] > 1 then null else [SCORE]), type table }})