Custom Column to Calculate a Change in dates

Hi PR1762 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjHUC0pNzywuSS1KTVFwSSxJVdJRMtQ31DcyMDJGZsbqgBUHlyQWlSDUGSHUGaGo8y9KSS1CqDNGqDNGUafgk5+enwdTCEJgCSMsrkKSRHEFkjguW2HyGLYZ6ptAnRMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, #"123459789" = _t, #"987654321" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"123459789", type date}, {"987654321", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(1999, 1, 1),Replacer.ReplaceValue,{"123459789", "987654321"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Type"}, "Attribute", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Columns",#date(1999, 1, 1),null,Replacer.ReplaceValue,{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Type]), "Type", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Attribute", "T1.Registered Date", "T2.Registered Date", "T1.Start Date", "T2.Start Date", "T1.Order Date", "T2.Order Date", "T1. Logon Date", "T2. Logon Date"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each let 
a=if [T1.Registered Date]=[T2.Registered Date] or [T2.Registered Date]=null then 1 else 0,
b=if [T1.Start Date]=[T2.Start Date] or [T2.Start Date]=null then 1 else 0,
c=if [T1.Order Date]=[T2.Order Date] or [T2.Order Date]=null then 1 else 0,
d=if [T1. Logon Date]= [T2. Logon Date] or [T2. Logon Date]=null then 1 else 0
in if a+b+c+d<4 then true else false)
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi PR1762,

Be sure:

1. your 1st column name is Column1
2. separator in this column is dot
3. you have only 2 tables (in this sample case T1 and T2)

Source:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjHUC0pNzywuSS1KTVFwSSxJVdJRMtQ31DcyMDJGZsbqgBUHlyQWlSDUGSHUGaGo8y9KSS1CqDNGqDNGUafgk5+enwdTCEJgCSMsrkKSRHEFkjguW2HyGLYZ6ptAnRMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"123459789" = _t, #"987654321" = _t]),
    CustomerID_ColNames = List.Buffer(List.Select(Table.ColumnNames(Source), each _ <> "Column1")),
    StepBack = Source,
    ChangedTypeDynamic = Table.TransformColumns(StepBack, List.Transform(CustomerID_ColNames, (colName)=>
      { colName, each Date.From(_, "en-US") , type date } ) ),
    #"Split Column by Delimiter" = Table.SplitColumn(ChangedTypeDynamic, "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Table", "Type"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Table", Text.Trim, type text}, {"Type", Text.Trim, type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Trimmed Text", {"Table", "Type"}, "Customer ID", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Type]), "Type", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Pivoted Column", "Index", 0, 1, Int64.Type),
    RaplaceNullDate = Table.ReplaceValue(#"Added Index",
     null,
     null,
     (x,y,z)=> if x = null then #date(1900,1,1) else x,
     List.Select(Table.ColumnNames(#"Added Index"), each not List.Contains({"Table", "Customer ID", "Index"}, _)) /* All Columns except */ ),
    // Add column [Keep Record]. If ANY of T2 date is greather than T1 value then TRUE else FALSE.
    #"Grouped Rows" = Table.Group(RaplaceNullDate, {"Customer ID"}, {{"Data", each Table.AddColumn(_, "Keep Record", (y)=> 
         List.AnyTrue(List.Accumulate(
                          List.Select(Table.ColumnNames(_), (x)=> not List.Contains({"Table", "Customer ID", "Index"}, x)),
                          {},
                         (s,c)=> s & { if _{0}[Table] = y[Table] then null else Record.Field(Table.SelectColumns(_, c){1}, c) > Record.Field(Table.SelectColumns(_, c){0}, c) } 
         ) ), type logical), type table}}),
    CombinedTables = Table.Combine(#"Grouped Rows"[Data]),
    #"Sorted Rows" = Table.Sort(CombinedTables,{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"