Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

return value qty for the most recent week of the month

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 dateitemForecast monthFcst qty
1/4/20181456jan4
1/4/20181456feb5
1/4/20181456mar4
1/4/20181456apr7
1/4/20181456may65
1/4/20181456jun4
1/4/20181456jul3
1/4/20181456aug47
1/4/20181456sep9
1/4/20181456oct4
1/4/20181456nov6
1/4/20181456dec7
1/11/20181456jan5
1/11/20181456feb7
1/11/20181456mar9
1/11/20181456apr1
1/11/20181456may2
1/11/20181456jun8
1/11/20181456jul3
1/11/20181456aug48
1/11/20181456sep69
1/11/20181456oct47
1/11/20181456nov65
1/11/20181456dec48

 

example of what i want returned in the new column

 

report dateitemForecast monthFcst qtynew column
1/4/20181456jan40
1/4/20181456feb50
1/4/20181456mar40
1/4/20181456apr70
1/4/20181456may650
1/4/20181456jun40
1/4/20181456jul30
1/4/20181456aug470
1/4/20181456sep90
1/4/20181456oct40
1/4/20181456nov60
1/4/20181456dec70
01/11/2181456jan55
1/11/20181456feb70
1/11/20181456mar90
1/11/20181456apr10
1/11/20181456may20
1/11/20181456jun80
1/11/20181456jul30
1/11/20181456aug480
1/11/20181456sep690
1/11/20181456oct470
1/11/20181456nov650
1/11/20181456dec480

 

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

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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"

1.PNG

 

Best regards,

Yuliana Gu

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

View solution in original post

2 REPLIES 2
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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"

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.