Solved: Re: Cleaning dates issue

GeekAlfPro · ‎01-18-2021

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

15/11/2020

7

50444

MAL

Nesbo

Jo

232029935023325

1

MAL

Maladie

16/11/2020

22/11/2020

7

50444

MAL

Nesbo

Jo

232029935023325

2

MAL

Maladie

23/11/2020

29/11/2020

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

06/11/2020

5

49555

MAL

Herbert

Frank

263062652200256

1

MALP

Prolongation maladie

02/11/2020

07/11/2020

13/11/2020

7

49555

MAL

Herbert

Frank

263062652200256

2

MALP

Prolongation maladie

02/11/2020

14/11/2020

28/11/2020

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

04/11/2020

3

49333

MAL

Mishima

Yukio

262222629333233

1

MAL

Maladie

18/11/2020

20/11/2020

3

49333

MAL

Mishima

Yukio

262222629333233

2

MALP

Prolongation maladie

18/11/2020

21/11/2020

24/11/2020

4

Thanks for your help !

AlB · ‎01-18-2021

@GeekAlfPro

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

View solution in original post

AlB · ‎01-18-2021

@GeekAlfPro

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

AlB · ‎01-18-2021

@GeekAlfPro

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

GeekAlfPro · ‎01-19-2021

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

AlB · ‎01-18-2021

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:

https://community.powerbi.com/t5/Power-Query/How-to-merge-two-rows-when-date-range-is-in-continuatio...

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