Custom Column to Calculate a Change in dates

[PR1762]

I am trying to create a custom column to see if there has been any changes in date fields in two tables (T1 and T2).  I have merge the tables and the below table gives the dates I would to check and flag to filter the data.

In the table below there are two customer I would like to check all dates to see if there is any change to each date in the second table, however when checking the dates I want to ignore the records where there is a null against a date as this illustrates that no change in dates.  So the first customer would get a false against the filter Keep Record,  however the second customer would have a true set, has the T2.Logon Date as a change to the record in the first table.

my starting code is in the code below, which does not completely work.

Customer ID	123456789		987654321
T1.Registered Date	01/01/2023		01/01/2023
T1.Start Date	01/02/2023		01/02/2023
T1.Order Date	01/03/2023		01/03/2023
T1. Logon Date	Null		Null
T2.Registered Date	Null		Null
T2.Start Date	Null		Null
T2.Order Date	01/02/2023		Null
T2. Logon Date	Null		01/04/2023
Keep Record	FALSE		TRUE

Started code

= Table.AddColumn(#"Ignore Erors", "DateCheck", each [Phase 1 Input Date] = [TMD.Phase 1 Input Date] and  [Phase 1 Output Date] = [TMD.Phase 1 Output Date] and [Phase 2 Output Date] = [TMD.Phase 2 Output Date] and [Discharged Date] = [TMD.Discharged Date])

thanks in advance Paul

[dufoq3]

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"

[Anonymous]

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.

[PR1762]

Many thanks for your response Yolo.

in your code how would I use this against a query that has a over a 1000 customers?  As I see in the query you direct change type of each customer id individually also replace the values.

paul

[Anonymous]

Hi @PR1762 

You can try the following code.

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]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Type"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", Int64.Type}, {"Value", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[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"

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.

[PR1762]

Many thanks.

If I have a source table already how do I take the code from the Hk"unpivoted columns" part of the code and insert into my steps?  I get an error when trying to achive this when I try to pivot the columns.

paul

[Anonymous]

Hi @PR1762 

Is your data the same as the sample you provided?

If the structure is the same as the sample data, in the sample data , your t1.date column does not have column name, so i name it Type

just replace the 'Type' to your own column name.

If the data structure is not same as the sample you provided, please provided some sample data of your current data structure.

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.