Forum Discussion
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.
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
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.