Forum Discussion
Custom Column to Calculate a Change in dates
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.
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
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.