Dates effective from and to

[MohammedAli]

Hi,

My data is in the following format

Emp ID	Pay Type	Effective From	Effective To	Amount
100001	Basic	1/Jan/2019	31/Jul/2019	1000
100001	Basic	1/Aug/2019	31/Dec/2019	1500
100001	HRA	1/Jan/2019	31/Jul/2019	100
100001	HRA	1/Aug/2019	31/Dec/2019	150

I want to transform the data to 

Emp ID	Month	Year	Basic	HRA
100001	Jan	2019	1000	100
100001	Feb	2019	1000	100
100001	Mar	2019	1000	100
100001	Apr	2019	1000	100
100001	May	2019	1000	100
100001	Jun	2019	1000	100
100001	Jul	2019	1000	100
100001	Aug	2019	1500	150
100001	Sep	2019	1500	150
100001	Oct	2019	1500	150
100001	Nov	2019	1500	150
100001	Dec	2019	1500	150

Reqeust you to please guide me in the correct direction.

Thanks in advance.

[v-juanli-msft · Accepted solution]

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.

[amitchandak]

@MohammedAli , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or this file

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0