Forum Discussion
Adding 60 onto each day value
Hi guys. Just wondering if someone could give me a hand
Currently I need to have a comulative value of 60 being added onto each day per month (Being reset back to 60 at the start of the new month)
So on the 1st the value is 60, second it's 120, 3rd its 180 and so on. However there are also days where the place is closed and therefore 60 doesn't need to be added.
I am currently racking my brain and have no idea how exactly I am meant to do this
If anyone could help me that would be great
Many thanks
Some fake sample data would have helped us understand your case.
In the screen shot below, the blue table is the input in Table1.
The green table is the table from query Result.
Query Table1 imports Table1 into Power Query.
Query Result:
let
Source = Table1,
#"Inserted End of Month" = Table.AddColumn(Source, "End of Month", each Date.EndOfMonth([Date]), type date),
#"Filtered Rows" = Table.SelectRows(#"Inserted End of Month", each ([OpenOrClosed] = "Open")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"End of Month"}, {{"AllData", each Table.AddIndexColumn(_,"Value",60,60), Value.Type(Table.AddColumn(#"Filtered Rows","Value",each 0, Int64.Type))}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Date", "OpenOrClosed", "Value"}, {"Date", "OpenOrClosed", "Value"}),
#"Merged Queries" = Table.NestedJoin(Source,{"Date"},#"Expanded AllData",{"Date"},"AllData",JoinKind.LeftOuter),
#"Expanded AllData1" = Table.ExpandTableColumn(#"Merged Queries", "AllData", {"Value"}, {"Value"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded AllData1",null, each if [Date] = Date.StartOfMonth([Date]) then 0 else null,Replacer.ReplaceValue,{"Value"}),
#"Restored Type" = Value.ReplaceType(#"Replaced Value",Value.Type(#"Expanded AllData1")),
#"Sorted Rows" = Table.Sort(#"Restored Type",{{"Date", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Value"})
in
#"Filled Down"
3 Replies
Some fake sample data would have helped us understand your case.
In the screen shot below, the blue table is the input in Table1.
The green table is the table from query Result.
Query Table1 imports Table1 into Power Query.
Query Result:
let
Source = Table1,
#"Inserted End of Month" = Table.AddColumn(Source, "End of Month", each Date.EndOfMonth([Date]), type date),
#"Filtered Rows" = Table.SelectRows(#"Inserted End of Month", each ([OpenOrClosed] = "Open")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"End of Month"}, {{"AllData", each Table.AddIndexColumn(_,"Value",60,60), Value.Type(Table.AddColumn(#"Filtered Rows","Value",each 0, Int64.Type))}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Date", "OpenOrClosed", "Value"}, {"Date", "OpenOrClosed", "Value"}),
#"Merged Queries" = Table.NestedJoin(Source,{"Date"},#"Expanded AllData",{"Date"},"AllData",JoinKind.LeftOuter),
#"Expanded AllData1" = Table.ExpandTableColumn(#"Merged Queries", "AllData", {"Value"}, {"Value"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded AllData1",null, each if [Date] = Date.StartOfMonth([Date]) then 0 else null,Replacer.ReplaceValue,{"Value"}),
#"Restored Type" = Value.ReplaceType(#"Replaced Value",Value.Type(#"Expanded AllData1")),
#"Sorted Rows" = Table.Sort(#"Restored Type",{{"Date", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Value"})
in
#"Filled Down"Hi Kassii,
Please use the Query MarcelBeug posted, which is same in Power BI. Just replace the table name and column name to yours. Then share your sample table model and list expected result if you still have problem needed to solve. So that we can share the detailed solution which is close to your requirement.
Thanks,
AngeliaHi Kassii,
Have you resolved your issue? Please share your solution or mark the helpful reply as answer if you have resoved it. More people will find workaround easily and quickly.
Thanks,
Angelia
