previous date

[EDO_01_1789]

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

[wdx223_Daniel]

NewStep=let a=Table.ToColumns(PreviousStepName) in Table.FromColumns(a&{{null}&List.RemoveLastN(List.Last(a))},Table.ColumnNames(PreviousStepName)&{"Previous Date"})

[EDO_01_1789]

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 4

I 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 column 

[jbwtp]

Hi @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

[jbwtp · Accepted solution]

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
    Custom1

N.B. you will need to use GroupBy to get this applied to the reviewed files in batches.

Cheers,

John