Insert blank/ 0 dollar record if there is no record for a given date

[ogend]

hello, 

I have a table that looks like this:

Period	Scenario	Department	Revenue
17-Dec	Actual	department1	500
18-Mar	Forecast	department1	550
18-Jun	Forecast	department1	600
18-Sep	Forecast	department1	500
18-Dec	Forecast	department1	530
18-Dec	Actual	department1	535
19-Mar	Forecast	department1	580
19-Jun	Forecast	department1	600
19-Sep	Forecast	department1	620
19-Dec	Forecast	department1	590
19-Dec	Actual	department1	600
20-Mar	Forecast	department1	620
20-Jun	Forecast	department1	630
20-Sep	Forecast	department1	620
20-Dec	Forecast	department1	610
20-Dec	Actual	department1	600
17-Dec	Actual	department2	575
18-Mar	Forecast	department2	633
18-Jun	Forecast	department2	690
18-Sep	Forecast	department2	575
18-Dec	Forecast	department2	610
18-Dec	Actual	department2	615
19-Mar	Forecast	department2	667
19-Jun	Forecast	department2	690
19-Sep	Forecast	department2	713
19-Dec	Forecast	department2	679
19-Dec	Actual	department2	690
20-Mar	Forecast	department2	713
20-Jun	Forecast	department2	725
20-Sep	Forecast	department2	713
20-Dec	Forecast	department2	702
20-Dec	Actual	department2	700
19-Dec	Actual	department 3	345
20-Mar	Forecast	department 3	357
20-Jun	Forecast	department 3	362
20-Sep	Forecast	department 3	357
20-Dec	Forecast	department 3	351
20-Dec	Actual	department 3	350

Department 3 Doesn't have any records prior to 2020. 

Would it be possilbe to do the following in Power query:  for each department for each  year where the recod where scenario "Actual" doesn't exist -insert a row with revenue equal to 0.

I am looking for this output- inserted two rows with 0s for dep 3

Period	Scenario	Department	Revenue
17-Dec	Actual	department1	500
18-Mar	Forecast	department1	550
18-Jun	Forecast	department1	600
18-Sep	Forecast	department1	500
18-Dec	Forecast	department1	530
18-Dec	Actual	department1	535
19-Mar	Forecast	department1	580
19-Jun	Forecast	department1	600
19-Sep	Forecast	department1	620
19-Dec	Forecast	department1	590
19-Dec	Actual	department1	600
20-Mar	Forecast	department1	620
20-Jun	Forecast	department1	630
20-Sep	Forecast	department1	620
20-Dec	Forecast	department1	610
20-Dec	Actual	department1	600
17-Dec	Actual	department2	575
18-Mar	Forecast	department2	633
18-Jun	Forecast	department2	690
18-Sep	Forecast	department2	575
18-Dec	Forecast	department2	610
18-Dec	Actual	department2	615
19-Mar	Forecast	department2	667
19-Jun	Forecast	department2	690
19-Sep	Forecast	department2	713
19-Dec	Forecast	department2	679
19-Dec	Actual	department2	690
20-Mar	Forecast	department2	713
20-Jun	Forecast	department2	725
20-Sep	Forecast	department2	713
20-Dec	Forecast	department2	702
20-Dec	Actual	department2	700
17-Dec	Actual	department 3	0
18-Dec	Actual	department 3	0
19-Dec	Actual	department 3	345
20-Mar	Forecast	department 3	357
20-Jun	Forecast	department 3	362
20-Sep	Forecast	department 3	357
20-Dec	Forecast	department 3	351
20-Dec	Actual	department 3	350

Thank you!

[Jimmy801]

Hello @ogend 

I saw that you canceled your reply. So where you able to tweak the code?

BR

Jimmy

[ogend]

Hi @Jimmy801 , 

Thanks a lot for your guidance, it was superhelpful!

[ogend]

Hello @Jimmy801 , 

Thanks a lot fot your help!

I attempted to tweak your code to make it work with my Excel source file, but unfortunately it does not populate 0 line rows

would you be able to to take a look?

let
    Source = Excel.Workbook(File.Contents("C:\Users\Olga\Sample.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Period", type date}, {"Scenario", type text}, {"Department", type text}, {"Revenue", Int64.Type}}),
//    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Scenario", type text}, {"Department", type text}, {"Revenue", Int64.Type}}, "de-DE"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Period]), type number),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Department", "Year"}, {{"AllRows", each _, type table [Period=date, Scenario=text, Department=text, Revenue=number, Year=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "HasActual", each if List.Contains([AllRows][Scenario],"Actual") then [AllRows] else Table.Combine({[AllRows], #table({"Department", "Year", "Period", "Scenario", "Revenue"},{{[Department], [Year], [AllRows][Period]{0}, "Actual", 0}})})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
    #"Expanded HasActual" = Table.ExpandTableColumn(#"Removed Columns", "HasActual", {"Period", "Scenario", "Revenue"}, {"Period", "Scenario", "Revenue"})
in
    #"Expanded HasActual"
//    #"Changed Type"

[Jimmy801]

Hello @ogend 

I cannot see any problems in the code. Are there some error messages? What is your final step showing? (Empty table or error). Could you make me please a screenshot of the data after the "Changed Type"-step. Did you check that your date-column is containing also a year? Could you share in case your sampel.xlsx?

BR

Jimmy

[Jimmy801 · Accepted solution]

Hello @ogend 

first of all in your period-column the year is missing. And there are also missing some specification like which period to assign to the new row etc. What I tried to do is to add a new column that extracts the year. Then I grouped by Year and Deparment adding a function that takes the whole table. Then I added a new column where I checked if I can find the word "Actual" in the scenario-column. If no, I added a row, using the data from the grouped table. This means that you need to have a record in that year but not containing "Actual". If so, I added it using the first period found of this year. Hope you can understand my approach. Here the code of my solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUMzDUMzIwtFTSUXLLL0pNTiwuATJTUgsSi0pyU/NKDIE8U1MDpVgdhGojA6CgY3JJaWIOploDLGopNtmIJJONYCbHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Scenario = _t, Department = _t, Revenue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Scenario", type text}, {"Department", type text}, {"Revenue", Int64.Type}}, "de-DE"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Period]), type number),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Department", "Year"}, {{"AllRows", each _, type table [Period=date, Scenario=text, Department=text, Revenue=number, Year=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "HasActual", each if List.Contains([AllRows][Scenario],"Actual") then [AllRows] else Table.Combine({[AllRows], #table({"Department", "Year", "Period", "Scenario", "Revenue"},{{[Department], [Year], [AllRows][Period]{0}, "Actual", 0}})})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
    #"Expanded HasActual" = Table.ExpandTableColumn(#"Removed Columns", "HasActual", {"Period", "Scenario", "Revenue"}, {"Period", "Scenario", "Revenue"})
in
    #"Expanded HasActual"

this transforms this

into this

adding in 2019 for dept1 a new row with 0 for actual using the 19.01.19 as period.

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[PC2790]

There may be better ways to do it but one the ones ways if:

If you have a continuous date table in Power Query having dates from the beinginning till the end, you can make a join of right outer join, the table above being on the left and date table on the right.

This will give you the missing dates in your table and null values.

You can replace null values according to your requirement