let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUohQ0lFyTC7JLEsFMgx1jXWNDIyMgcyi0ry8zLx0IEvhUdMaBWRgamCgY2CgFKsDMSESqMYvv0QByRRTkCmGQKaxoa4FjI1hjjGqOVEoLjGDGELAKSZQI2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Object = _t, Status = _t, Startdate = _t, Enddate = _t, #" Budget RO pm " = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"running",null,Replacer.ReplaceValue,{"Enddate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Startdate", type date}, {"Enddate", type date}, {" Budget RO pm ", Currency.Type}},"nl"),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Months", (k)=> List.Generate(()=>k[Startdate],each _ < (k[Enddate] ?? Date.From(DateTime.LocalNow())), each Date.AddMonths(_,1),each Date.StartOfMonth(_))),
#"Expanded Months" = Table.ExpandListColumn(#"Added Custom", "Months"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Months",{{"Months", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Budget pm", each if Date.Year([Startdate]) = Date.Year([Months])
and Date.Month([Startdate]) = Date.Month([Months])
and Date.Day([Startdate])<> Date.Day([Months])
then Duration.Days(Date.AddMonths([Months],1)-[Startdate])/Duration.Days(Date.AddMonths([Months],1)-[Months])*[#" Budget RO pm "]
else [#" Budget RO pm "],Currency.Type)
in
#"Added Custom1"
Cómo usar este código: Crear una nueva consulta en blanco. Haga clic en "Editor avanzado". Reemplace el código en la ventana con el código proporcionado aquí. Haga clic en "Listo".
