Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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
thank you so much , i knew i had to use group by function like in sql in some way but was not sure how
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
100 | |
95 | |
38 | |
37 |
User | Count |
---|---|
152 | |
125 | |
75 | |
74 | |
63 |