Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
I hope that my request has been well explained and that you could help me please
thanks
Solved! Go to 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
NewStep=let a=Table.ToColumns(PreviousStepName) in Table.FromColumns(a&{{null}&List.RemoveLastN(List.Last(a))},Table.ColumnNames(PreviousStepName)&{"Previous Date"})
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
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
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