Duplicate IDs with criteria

hi deannapi ,

 

create a blank query and copy paste the below code into the advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lEKT00Ckob6hvpGBkYmSrE6UHH/kozUIrCMEZKMMU4ZEySzjNHEETqQZUzxysDMMsGpA1nGDKeMOQ6zkMVNkcQt8IgjbIDJxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Origin = _t, Date = _t]),
    //merge date and id columns
    #"Date-ID concatenation" = Table.AddColumn(Source, "Date -ID", each Text.Combine({[Date], [ID]}, " - "), type text),
    #"Changed Type" = Table.TransformColumnTypes(#"Date-ID concatenation",{{"ID", Int64.Type}, {"Origin", type text}, {"Date", type date}}),
    // group all data by date and id column
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date -ID"}, {{"Data", each _, type table [ID=nullable number, Origin=nullable text, Date=nullable date, #"Date -ID"=text]}}),
    // criteria to check if multiple ids in the same day
    ReplaceID = Table.AddColumn(#"Grouped Rows", "ReplaceID", each not( List.Count([Data][ID]) = List.Count(List.Distinct([Data][ID])))),
    // Get data which doesn't need to be replaced. Also, replace Origin that has multiple ids in same day
    Data_New = Table.AddColumn(ReplaceID, "Data_New", each if [ReplaceID]= true then Table.AddColumn(Table.RemoveColumns([Data],"Origin"),"Origin", each "Other") else [Data]),
    #"Removed Other Columns" = Table.SelectColumns(Data_New,{"Data_New"}),
    #"Expanded Data_New" = Table.ExpandTableColumn(#"Removed Other Columns", "Data_New", {"ID", "Origin", "Date"}, {"ID", "Origin", "Date"})
in
    #"Expanded Data_New"

tried to solve this via Power Query. let me know if this is the expected output below:

 

steps:

1. created a merged date-id column

2. group all data by this column

3. applied a check to see if there are multiple ids on that date-id combination

4. if yes, replace "Web" with "Other", else give the Data without replacement.

5. expanded the "New_Data" Table with the changes.