Dates effective from and to

Hi MohammedAli 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAkMlHSWnxOLMZCBtqO+VmKdvZGBoCeQYA3mlOTAeSK1SrA5WTY6l6UiaXFKT4ZpM0TV5BDkStAe7DnyWKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp ID" = _t, #"Pay Type" = _t, #"Effective From" = _t, #"Effective To" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp ID", Int64.Type}, {"Pay Type", type text}, {"Effective From", type date}, {"Effective To", type date}, {"Amount", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Pay Type"]), "Pay Type", "Amount"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each List.Dates([Effective From],Duration.Days([Effective To]-[Effective From])+1,#duration(1,0,0,0))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Select([Custom],each Date.Day(_)=1)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "new date"}}),
    #"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([new date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([new date]), type text),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}})
in
    #"Extracted First Characters"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.