Forum Discussion
previous date
Hello,
I would like to create a New column that will allow me to have the time of the previous validator.
here is the result I would like to have.
in my table there are between 5 and 4 validators (column with number 2) by Excel file (column with number 1).
What I want is that in the line of the validator 2, I can have the time (column with number 3) and the date of validation of the validator 1 of the same excel file).
I don't know if it's ideal to do it in M or in DAX ?
for your information:
- would it be possible not to modify the formatting (no unpivot or transpose transformation).
- validator 1 has no pre-date since it is the creator.
I hope that my request has been well explained and that you could help me please
thanks
Hi EDO_01_1789,
Something like this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUNzTSN7Q0N1eK1QFzDYxBXAsI10DfwAyVa4nEhekFcmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Validator Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Validator Date", type date}}), Custom1 = Table.FromColumns(Table.ToColumns(#"Changed Type") & { {""} & List.RemoveLastN(#"Changed Type"[Validator Date],1)}, Value.Type(Table.AddColumn(#"Changed Type", "Previous Date", each null, type date))) in Custom1N.B. you will need to use GroupBy to get this applied to the reviewed files in batches.
Cheers,
John
4 Replies
Hi EDO_01_1789,
Something like this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUNzTSN7Q0N1eK1QFzDYxBXAsI10DfwAyVa4nEhekFcmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Validator Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Validator Date", type date}}), Custom1 = Table.FromColumns(Table.ToColumns(#"Changed Type") & { {""} & List.RemoveLastN(#"Changed Type"[Validator Date],1)}, Value.Type(Table.AddColumn(#"Changed Type", "Previous Date", each null, type date))) in Custom1N.B. you will need to use GroupBy to get this applied to the reviewed files in batches.
Cheers,
John
Hello jbwtp ,
yes I would like a solution like that.
I just want to make sure that the validator 1 of each excel file does not have a previous date.
The validator 2 must have the validation date of the validator 1
The validator 3 must have the validation date of the validator 2
Validator 4 must have the validation date of validator 3
The validator 5 must have the validation date of the validator 4I have put below a table of what I want :
Fichier Excel Validator Name Validation date Previous validator date Excel File A Validator n° 1 11/08/2022 Excel File A Validator n° 2 12/08/2022 11/08/2022 Excel File A Validator n° 3 13/08/2022 12/08/2022 Excel File A Validator n° 4 14/08/2022 13/08/2022 Excel File A Validator n° 5 15/08/2022 14/08/2022 Excel File B Validator n° 1 02/08/2022 15/08/2022 Excel File B Validator n° 2 03/08/2022 02/08/2022 Excel File B Validator n° 3 04/08/2022 03/08/2022 Excel File B Validator n° 4 05/08/2022 04/08/2022 Excel File B Validator n° 5 06/08/2022 05/08/2022 Excel File C Validator n° 1 01/07/2022 06/08/2022 Excel File C Validator n° 2 02/07/2022 01/07/2022 Excel File C Validator n° 3 03/07/2022 02/07/2022 Excel File C Validator n° 4 04/07/2022 03/07/2022 Excel File C Validator n° 5 05/07/2022 04/07/2022
The column I am trying to create is the last columnHi EDO_01_1789,
as you said "I just want to make sure that the validator 1 of each excel file does not have a previous date" I assume that rows 6 & 11 in your example are incorrect (where it switches from A to B and from B to C)?
This is the solution to get what I thin kyou want beying that all Validator 1 have null in the Previous column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZJNCsIwEEavErIu5L9xq6JHcFO6KNpFISiIC4/TI3iGnMwOsTrSEjLLhLwH3yNNw+N4eJ77wI5D6OPItrzipy4Ml+5xu7NrfDE13Sgl5EZoqfV04G1VgMFLpRGGHEUCA4zBAk0TWGAsFhiawAHjsMDmBLvVdH8JHI0HBhfIBljiUBDvz85f4tAPryeOh3o1wrPb9+vfTkg/4zUN/+T64j9VEW5SLo/KU3CbcnlUnoK7lMuj8jPevgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Fichier Excel" = _t, #"Validator Name" = _t, #"Validation date" = _t, #"Previous validator date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fichier Excel", type text}, {"Validator Name", type text}, {"Validation date", type date}, {"Previous validator date", type date}}), fAddPVD = (t as table) as table => let #"Sorted Rows" = Table.Sort(t,{{"Validator Name", Order.Ascending}}), Result = Table.FromColumns(Table.ToColumns(#"Sorted Rows") & { {""} & List.RemoveLastN(#"Sorted Rows"[Validation date],1)}, Value.Type(Table.AddColumn(#"Sorted Rows", "Previous Date", each null, type date))) in Result, #"Grouped Rows" = Table.Group(#"Changed Type", {"Fichier Excel"}, {{"Count", each fAddPVD(_), Value.Type(Table.AddColumn(#"Changed Type", "Previous Date", each null, type date))}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Validator Name", "Validation date", "Previous validator date", "Previous Date"}, {"Validator Name", "Validation date", "Previous validator date", "Previous Date"}) in #"Expanded Count"This also assumes that there is no special rules to deal with gaps in the numbreing (e.g. if validator 3 is missing on some files).
Kind regards,
John
NewStep=let a=Table.ToColumns(PreviousStepName) in Table.FromColumns(a&{{null}&List.RemoveLastN(List.Last(a))},Table.ColumnNames(PreviousStepName)&{"Previous Date"})
