Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there,
i've a file, i need to clean.
in my samples data below, i have to keep the last date (Justif - Date fin absence) by people, but only if the column (Regroupement) is the same, and if the date date are in the same periode.
e.g. the first 4 lines should be one line because the dates follow each other (09-11-2020 to 07-12-2020)
inversely, for the last 3 lines, i should have 2 lines (02-11-2020 to 04-11-2020) and (18-11-2020 to 24-11-2020)
Matricule | Regroupement | Nom | Prénom | N° SS | Index | Justification (Code) | Justification (Libellé) | Date d'arrêt initial (MAL et MALP) | Justif - Date début absence | Justif - Date fin absence | Nb jours sur la période sélectionnée | Nb jours total par arrêt |
50444 | MAL | Nesbo | Jo | 232029935023325 | 0 | MAL | Maladie | 09/11/2020 | 09/11/2020 | 15/11/2020 | 7 | 7 |
50444 | MAL | Nesbo | Jo | 232029935023325 | 1 | MAL | Maladie | 16/11/2020 | 16/11/2020 | 22/11/2020 | 7 | 7 |
50444 | MAL | Nesbo | Jo | 232029935023325 | 2 | MAL | Maladie | 23/11/2020 | 23/11/2020 | 29/11/2020 | 7 | 7 |
50444 | MAL | Nesbo | Jo | 232029935023325 | 3 | MALP | Prolongation maladie | 23/11/2020 | 30/11/2020 | 07/12/2020 | 1 | 8 |
49999 | AT | Ecco | Umberto | 262052652026222 | 0 | TVLP | Prolongation AT | null | 23/10/2020 | 22/11/2020 | 22 | 31 |
49999 | AT | Ecco | Umberto | 262052652026222 | 1 | TVLP | Prolongation AT | null | 23/11/2020 | 21/12/2020 | 8 | 29 |
49555 | MAL | Herbert | Frank | 263062652200256 | 0 | MAL | Maladie | 02/11/2020 | 02/11/2020 | 06/11/2020 | 5 | 5 |
49555 | MAL | Herbert | Frank | 263062652200256 | 1 | MALP | Prolongation maladie | 02/11/2020 | 07/11/2020 | 13/11/2020 | 7 | 7 |
49555 | MAL | Herbert | Frank | 263062652200256 | 2 | MALP | Prolongation maladie | 02/11/2020 | 14/11/2020 | 28/11/2020 | 15 | 15 |
49555 | MAL | Herbert | Frank | 263062652200256 | 3 | MALP | Prolongation maladie | 02/11/2020 | 29/11/2020 | 12/12/2020 | 2 | 14 |
49333 | MAL | Mishima | Yukio | 262222629333233 | 0 | MAL | Maladie | 02/11/2020 | 02/11/2020 | 04/11/2020 | 3 | 3 |
49333 | MAL | Mishima | Yukio | 262222629333233 | 1 | MAL | Maladie | 18/11/2020 | 18/11/2020 | 20/11/2020 | 3 | 3 |
49333 | MAL | Mishima | Yukio | 262222629333233 | 2 | MALP | Prolongation maladie | 18/11/2020 | 21/11/2020 | 24/11/2020 | 4 | 4 |
Thanks for your help !
Solved! Go to Solution.
Yes, it´s aexactly the same as what was done in the thread I pointed to earlier. Place the following M code in a blank query to see the steps. The forst two steps are just to enter your sample data. #"Add Column" is the crucial step. It could be made into a function to make it more legible
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRNb4MwDIb/ysS5EomdBDj2sGma1qmHbtJUcaAt2lD5kGj7/2dSBM7GxKBDsoOR4XHsN2y3nhZKKW/hrZbP5F/S066i9alxgCAgilALQARNT0SXuEry5JClzbPIl9KnTPE9kJoFgbV4MZEoB4jScAgPAG4nwgARkEOcILqdiNfENS3rusqr8iM5Z1V5VwzzUfCGB76ErhdkoeWriC6Klhty9/t9A34tdml9tiUYEBqMptcMALSD3bz9LMG+X17yvC1CDHbafgLlLLL8I7mHSbbj0I6gBWutu5Y/pnXDpLuHOimPFo3CNGgQArT5Tc18X27AhaatzcPK0XG73ICrHQflNr0ImFaEVHwCoXPIr25eGePSd8pwThupoBeClZJqq0DEfrTZ6TMrErp7vxyzVoN0GWjSwGZOFALvBVqbhx38tzm95QGIf8KOj97lSh7wvStrcfwF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Matricule = _t, Regroupement = _t, Nom = _t, Prénom = _t, #"N° SS" = _t, Index = _t, #"Justification (Code)" = _t, #"Justification (Libellé)" = _t, #"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)" = _t, #"Justif - Date début absence" = _t, #"Justif - Date fin absence" = _t, #"Nb jours sur la période sélectionnée" = _t, #"Nb jours total par arrêt" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Matricule", Int64.Type}, {"Regroupement", type text}, {"Nom", type text}, {"Prénom", type text}, {"N° SS", type text}, {"Index", Int64.Type}, {"Justification (Code)", type text}, {"Justification (Libellé)", type text}, {"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", type date}, {"Justif - Date début absence", type date}, {"Justif - Date fin absence", type date}, {"Nb jours sur la période sélectionnée", Int64.Type}, {"Nb jours total par arrêt", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Matricule"}, {{"Grouped", each _, type table [Matricule=nullable number, Regroupement=nullable text, Nom=nullable text, Prénom=nullable text, #"N° SS"=nullable text, Index=nullable number, #"Justification (Code)"=nullable text, #"Justification (Libellé)"=nullable text, #"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)"=nullable date, #"Justif - Date début absence"=nullable date, #"Justif - Date fin absence"=nullable date, Nb jours sur la période sélectionnée=nullable number, Nb jours total par arrêt=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let inputT_ =[Grouped],
colStart_ = inputT_[#"Justif - Date début absence"],
colEnd_ = inputT_[#"Justif - Date fin absence"],
S1_ = Table.AddIndexColumn(inputT_,"Index2",0),
S2_ = Table.AddColumn(S1_, "Date1", each if try colEnd_{[Index2]-1}=Date.AddDays([#"Justif - Date début absence"],-1) otherwise false then null else [#"Justif - Date début absence"], type date),
S3_ = Table.AddColumn(S2_, "Date2", each if try colStart_{[Index2]+1}=Date.AddDays([#"Justif - Date fin absence"], 1) otherwise false then null else [#"Justif - Date fin absence"], type date),
#"Filled Up" = Table.FillUp(S3_,{"Date2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Date1] <> null)
in
#"Filtered Rows"),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Regroupement", "Nom", "Prénom", "N° SS", "Index", "Justification (Code)", "Justification (Libellé)", "Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt", "Index2", "Date1", "Date2"}, {"Regroupement", "Nom", "Prénom", "N° SS", "Index", "Justification (Code)", "Justification (Libellé)", "Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt", "Index2", "Date1", "Date2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Grouped", "Index2", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date1", "Justif - Date début absence"}, {"Date2", "Justif - Date fin absence"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Justif - Date début absence", type date}, {"Justif - Date fin absence", type date}})
in
#"Changed Type1"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Yes, it´s aexactly the same as what was done in the thread I pointed to earlier. Place the following M code in a blank query to see the steps. The forst two steps are just to enter your sample data. #"Add Column" is the crucial step. It could be made into a function to make it more legible
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRNb4MwDIb/ysS5EomdBDj2sGma1qmHbtJUcaAt2lD5kGj7/2dSBM7GxKBDsoOR4XHsN2y3nhZKKW/hrZbP5F/S066i9alxgCAgilALQARNT0SXuEry5JClzbPIl9KnTPE9kJoFgbV4MZEoB4jScAgPAG4nwgARkEOcILqdiNfENS3rusqr8iM5Z1V5VwzzUfCGB76ErhdkoeWriC6Klhty9/t9A34tdml9tiUYEBqMptcMALSD3bz9LMG+X17yvC1CDHbafgLlLLL8I7mHSbbj0I6gBWutu5Y/pnXDpLuHOimPFo3CNGgQArT5Tc18X27AhaatzcPK0XG73ICrHQflNr0ImFaEVHwCoXPIr25eGePSd8pwThupoBeClZJqq0DEfrTZ6TMrErp7vxyzVoN0GWjSwGZOFALvBVqbhx38tzm95QGIf8KOj97lSh7wvStrcfwF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Matricule = _t, Regroupement = _t, Nom = _t, Prénom = _t, #"N° SS" = _t, Index = _t, #"Justification (Code)" = _t, #"Justification (Libellé)" = _t, #"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)" = _t, #"Justif - Date début absence" = _t, #"Justif - Date fin absence" = _t, #"Nb jours sur la période sélectionnée" = _t, #"Nb jours total par arrêt" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Matricule", Int64.Type}, {"Regroupement", type text}, {"Nom", type text}, {"Prénom", type text}, {"N° SS", type text}, {"Index", Int64.Type}, {"Justification (Code)", type text}, {"Justification (Libellé)", type text}, {"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", type date}, {"Justif - Date début absence", type date}, {"Justif - Date fin absence", type date}, {"Nb jours sur la période sélectionnée", Int64.Type}, {"Nb jours total par arrêt", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Matricule"}, {{"Grouped", each _, type table [Matricule=nullable number, Regroupement=nullable text, Nom=nullable text, Prénom=nullable text, #"N° SS"=nullable text, Index=nullable number, #"Justification (Code)"=nullable text, #"Justification (Libellé)"=nullable text, #"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)"=nullable date, #"Justif - Date début absence"=nullable date, #"Justif - Date fin absence"=nullable date, Nb jours sur la période sélectionnée=nullable number, Nb jours total par arrêt=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let inputT_ =[Grouped],
colStart_ = inputT_[#"Justif - Date début absence"],
colEnd_ = inputT_[#"Justif - Date fin absence"],
S1_ = Table.AddIndexColumn(inputT_,"Index2",0),
S2_ = Table.AddColumn(S1_, "Date1", each if try colEnd_{[Index2]-1}=Date.AddDays([#"Justif - Date début absence"],-1) otherwise false then null else [#"Justif - Date début absence"], type date),
S3_ = Table.AddColumn(S2_, "Date2", each if try colStart_{[Index2]+1}=Date.AddDays([#"Justif - Date fin absence"], 1) otherwise false then null else [#"Justif - Date fin absence"], type date),
#"Filled Up" = Table.FillUp(S3_,{"Date2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Date1] <> null)
in
#"Filtered Rows"),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Regroupement", "Nom", "Prénom", "N° SS", "Index", "Justification (Code)", "Justification (Libellé)", "Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt", "Index2", "Date1", "Date2"}, {"Regroupement", "Nom", "Prénom", "N° SS", "Index", "Justification (Code)", "Justification (Libellé)", "Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt", "Index2", "Date1", "Date2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Grouped", "Index2", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date1", "Justif - Date début absence"}, {"Date2", "Justif - Date fin absence"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Justif - Date début absence", type date}, {"Justif - Date fin absence", type date}})
in
#"Changed Type1"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AlB
thanks a lot, the result is exactly what i need !
i wouldn't have found it on my own.
thanks again and have a good day
Hi @GeekAlfPro
Can you share that table in an excel file? I cannot quite copy it correctly, with the aligned column names. And please include also a second table with the expected result.
You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
We recently solved something that is similar. Have a look:
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
User | Count |
---|---|
9 | |
7 | |
5 | |
5 | |
4 |
User | Count |
---|---|
14 | |
13 | |
8 | |
6 | |
6 |