return value qty for the most recent week of the month

[Anonymous]

Hi,

I recieve a .csv file on a weekly basis that contains a 12 month forecast that i upload into pbi. i would like to add a column  in power query that returns the monthly value from the most recent forecast of the month ie. the 12 month forecast for the last week of jan 18 ,feb 18, march 18

current querry example

report date	item	Forecast month	Fcst qty
1/4/2018	1456	jan	4
1/4/2018	1456	feb	5
1/4/2018	1456	mar	4
1/4/2018	1456	apr	7
1/4/2018	1456	may	65
1/4/2018	1456	jun	4
1/4/2018	1456	jul	3
1/4/2018	1456	aug	47
1/4/2018	1456	sep	9
1/4/2018	1456	oct	4
1/4/2018	1456	nov	6
1/4/2018	1456	dec	7
1/11/2018	1456	jan	5
1/11/2018	1456	feb	7
1/11/2018	1456	mar	9
1/11/2018	1456	apr	1
1/11/2018	1456	may	2
1/11/2018	1456	jun	8
1/11/2018	1456	jul	3
1/11/2018	1456	aug	48
1/11/2018	1456	sep	69
1/11/2018	1456	oct	47
1/11/2018	1456	nov	65
1/11/2018	1456	dec	48

example of what i want returned in the new column

report date	item	Forecast month	Fcst qty	new column
1/4/2018	1456	jan	4	0
1/4/2018	1456	feb	5	0
1/4/2018	1456	mar	4	0
1/4/2018	1456	apr	7	0
1/4/2018	1456	may	65	0
1/4/2018	1456	jun	4	0
1/4/2018	1456	jul	3	0
1/4/2018	1456	aug	47	0
1/4/2018	1456	sep	9	0
1/4/2018	1456	oct	4	0
1/4/2018	1456	nov	6	0
1/4/2018	1456	dec	7	0
01/11/218	1456	jan	5	5
1/11/2018	1456	feb	7	0
1/11/2018	1456	mar	9	0
1/11/2018	1456	apr	1	0
1/11/2018	1456	may	2	0
1/11/2018	1456	jun	8	0
1/11/2018	1456	jul	3	0
1/11/2018	1456	aug	48	0
1/11/2018	1456	sep	69	0
1/11/2018	1456	oct	47	0
1/11/2018	1456	nov	65	0
1/11/2018	1456	dec	48	0

as you can se in the example in the new columm the most recent report for jan is 1-11-2018 , so the new column would only show the the qty for jan of the forecast month, so iwant to be able to keep the latest qty for each report month ie latest jan fct for jan report,  latest feb fcst for feb report ect.

If you have any questions please let me know

thank you

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

let
    Source = Excel.Workbook(File.Contents("C:\Users\yulianag\Desktop\Sample Data.xlsx"), null, true),
    Sheet6_Sheet = Source{[Item="Sheet6",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet6_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"report date", type date}, {"item", Int64.Type}, {"Forecast month", type text}, {"Fcst qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Text.Lower(Date.ToText([report date],"MMM"))),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Month"}, {{"all", each _, type table}, {"Max date per month", each List.Max([report date]), type date}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"report date", "item", "Forecast month", "Fcst qty"}, {"all.report date", "all.item", "all.Forecast month", "all.Fcst qty"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded all",{{"all.report date", "report date"}, {"all.item", "item"}, {"all.Forecast month", "Forecast month"}, {"all.Fcst qty", "Fcst qty"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "New Column", each if [Max date per month]=[report date] and [Month]=[Forecast month] then [Fcst qty] else 0)
in
    #"Added Custom1"

Best regards,

Yuliana Gu

[Anonymous]

thank you so much , i knew i had to use group by function like in sql in some way but was not sure how