Forum Discussion
Define next date and previous date power query
Hi all, I need to develop a condition( ( 2 diff columns) for the next date from a column and previous date separated as the example below:
If the date of the column data photo clarity is higher than today show the next date from the column
EX: if Today is 20/11/2024 then the new column will show 07/12/2024 ( Next date)
if Today is 20/11/2024 the new column will show 02/11/2024 ( previous date)
Tks in advance!!
Hi,
Thanks for the solution ronrsnfld and Omid_Motamedise offered, and i want to offer some more infotmaiton for user to refer to.
hello fbittencourt , you cna create a blank query and input the following code to advanced editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7BDcAgCIXhXTib4ENQO4vp/muU4gGuX4CfcwjUaDJYuii97ZA4DJaEEROaoA7KlmAO4JkwY2UlrDi6E7aDMEr3cVmMEkaPUMzYFURJikjIKPL/azfu8n4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [num_photo = _t, #"Date photo clarity" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"num_photo", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date photo clarity", type date}}, "en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let a=#"Changed Type with Locale"[Date photo clarity], //#"Changed Type with Locale" is the last step name,replace it with your last step name b=Date.From(DateTime.LocalNow()), c=[num_photo]-1, d=List.Generate( ()=>[x=1,y=if a{0}<b then null else a{1}], each [x]<=List.Count(a), each [y=try if a{[x]}<b then a{[x]-1} else a{[x]+1} otherwise null,x=[x]+1], each [y]) in d{c}) 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 fbittencourt
Please try the the following code.
let Source = Excel.Workbook(File.Contents("C:\Users\h22012\OneDrive -88-Power BI\Dev DataSource\Autres\REF_Date-Photo-Clarity.xlsx"), null, true), Feuil1_Sheet = Source{[Item="Feuil1",Kind="Sheet"]}[Data], #"En-têtes promus" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"En-têtes promus",{{"Date photo clarity", type date},{"num_photo", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=#"Changed Type"[Date photo clarity], b=Date.From(DateTime.LocalNow()), c=[num_photo]-1, d=List.Generate( ()=>[x=1,y=if a{0}<b then null else a{1}], each [x]<=List.Count(a), each [y=try if a{[x]}<b then a{[x]-1} else a{[x]+1} otherwise null,x=[x]+1], each [y]) in d{c}), 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.
11 Replies
It's not clear what you are asking. I can't see how the example you've given and the image of data you provided are connected. I don't see any date 20/11/2024 in the photo.
Please restate the problem and give an example that is related to sample data that you provide - please provide a file or table of data not an image.
Regards
Phil
Again more simple:
Ex: 1- Today is 20/11/24 -
I need to show the first previous date from the column DAte photo Clarity in one column ( 02/11/2024)
and
I need to show the first next date from the column Date photo Clarity in one column ( 07/12/2024)
Paste the below code into your Advanced Editor to see one way of doing things.
Then replace the lines where I create a sample table (Lines Source and above) with your own data.
let //create example table // replace below lines with your own source data Days = Table.TransformColumnTypes( Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlOK1YlWMgaTRmASImICJg2RRCBqzMGkKZJ6cwz1hkhqcOnCakssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t]), {"Day", Int64.Type}), Months = List.Combine({{1..12},{1..12}}), Year = List.Combine({List.Repeat({2024},12), List.Repeat({2025},12)}), YMD = List.Transform(List.Zip({Year,Months,Days[Day]}), each #date(_{0},_{1},_{2})), Source = Table.FromColumns( {{1..24}} & {YMD} & {Year} & {Months}, type table[num_photo=Int64.Type, Date photo clarity=date, Année=Int64.Type, Mois=Int64.Type]), //next two steps may not be needed if dates are already sorted // and also if num_photo could be used as an Index #"Sorted Rows" = Table.Sort(Source,{{"Date photo clarity", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), #"Added Teste" = Table.AddColumn(#"Added Index", "Teste 1", each if [Index] = 0 then null else if [Date photo clarity] < Date.From(DateTimeZone.FixedLocalNow()) then #"Added Index"[Date photo clarity]{[Index]-1}? else #"Added Index"[Date photo clarity]{[Index]+1}?, type nullable date), #"Removed Columns" = Table.RemoveColumns(#"Added Teste",{"Index"}) in #"Removed Columns"Results from your posted date
Unlike your posted data, note that the first and last rows cannot be calcualted.
Next Day= DateTime.LocalNow()+Duration.From(1)
Previous Day= DateTime.LocalNow()+Duration.From(-1)
Hi,
Thanks for the solution ronrsnfld and Omid_Motamedise offered, and i want to offer some more infotmaiton for user to refer to.
hello fbittencourt , you cna create a blank query and input the following code to advanced editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7BDcAgCIXhXTib4ENQO4vp/muU4gGuX4CfcwjUaDJYuii97ZA4DJaEEROaoA7KlmAO4JkwY2UlrDi6E7aDMEr3cVmMEkaPUMzYFURJikjIKPL/azfu8n4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [num_photo = _t, #"Date photo clarity" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"num_photo", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date photo clarity", type date}}, "en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let a=#"Changed Type with Locale"[Date photo clarity], //#"Changed Type with Locale" is the last step name,replace it with your last step name b=Date.From(DateTime.LocalNow()), c=[num_photo]-1, d=List.Generate( ()=>[x=1,y=if a{0}<b then null else a{1}], each [x]<=List.Count(a), each [y=try if a{[x]}<b then a{[x]-1} else a{[x]+1} otherwise null,x=[x]+1], each [y]) in d{c}) 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.
Thanks fou your response , but I have a little issue, could you check where is my error?
First step:
2 step: my code:
Source = Excel.Workbook(File.Contents("C:\Users\h22012\OneDrive -88-Power BI\Dev DataSource\Autres\REF_Date-Photo-Clarity.xlsx"), null, true),
Feuil1_Sheet = Source{[Item="Feuil1",Kind="Sheet"]}[Data],
#"En-têtes promus" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"En-têtes promus", "Custom", each let a=[Date photo clarity],b=Date.From(DateTime.LocalNow()),
c=[num_photo]-1,
d=List.Generate(
()=>[x=1,y=if a{0}<b then null else a{1}],
each [x]<=List.Count(a),
each [y=try if a{[x]}<b then a{[x]-1} else a{[x]+1} otherwise null,x=[x]+1],
each [y])
in d{c}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date photo clarity", type date}})
in
#"Changed Type"3 step the error
Hi fbittencourt
Please try the the following code.
let Source = Excel.Workbook(File.Contents("C:\Users\h22012\OneDrive -88-Power BI\Dev DataSource\Autres\REF_Date-Photo-Clarity.xlsx"), null, true), Feuil1_Sheet = Source{[Item="Feuil1",Kind="Sheet"]}[Data], #"En-têtes promus" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"En-têtes promus",{{"Date photo clarity", type date},{"num_photo", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=#"Changed Type"[Date photo clarity], b=Date.From(DateTime.LocalNow()), c=[num_photo]-1, d=List.Generate( ()=>[x=1,y=if a{0}<b then null else a{1}], each [x]<=List.Count(a), each [y=try if a{[x]}<b then a{[x]-1} else a{[x]+1} otherwise null,x=[x]+1], each [y]) in d{c}), 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.
try this
let
Source = Excel.Workbook(File.Contents("C:\Users\ferna\OneDrive\Documentos\Data photo clarity.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"num_photo", Int64.Type}, {"Date photo clarity", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Today's date", each DateTime.Date(DateTime.LocalNow()),let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [num_photo = _t, #"Date photo clarity" = _t, Année = _t, Mois = _t, Index = _t, #"Today's date" = _t]),
InsertedDateSubtraction = Table.AddColumn(#"Added Custom", "Subtraction", each Duration.Days([Date photo clarity] - [#"Today's date"]), Int64.Type),
CalculatedMax = List.Max(List.Transform(InsertedDateSubtraction[Subtraction],(a)=>if a <0 then a else null)),
CalculatedMin = List.Min(List.Transform(InsertedDateSubtraction[Subtraction],(a)=>if a >0 then a else null)),
AddedCustom = Table.AddColumn(InsertedDateSubtraction, "Nearest Before", each if [Subtraction] = CalculatedMax then [Date photo clarity] else null,type date),
AddedCustom1 = Table.AddColumn(AddedCustom, "Nearest After", each if [Subtraction]=CalculatedMin then [Date photo clarity] else null,type date),
RemovedOtherColumns = Table.SelectColumns(AddedCustom1,{"num_photo", "Date photo clarity", "Index", "Nearest Before", "Nearest After"}),
#"Added Custom1" = Table.AddColumn(RemovedOtherColumns, "Today's date", each DateTime.Date(DateTime.LocalNow())),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"num_photo", "Date photo clarity", "Index", "Today's date", "Nearest Before", "Nearest After"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Today's date", type date}, {"Date photo clarity", type text}, {"Nearest Before", type text}, {"Nearest After", type text}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Today's date", type text}})
in
#"Changed Type2"
