Add missing rows for missing numbers

[Anonymous]

Hello, 

Can you please help me how to add rows that are missing in the current table?

In below picture, I am missing;

Accident Year 1997, Period 9,

Accident Year 1997, Period 10,

Accident Year 1999, Period 19,

Accident Year 2000, Period 19,

Accident Year 2000, Period 17,

Accident Year 2002, Period 16.

How do I add these values into the table using append or other method? I CANNOT manually enter them because the source is from SQL database. 

How can I create a table that has multiple years with multiple period? (i.e. Accident Year 2000, Period 1, Accident Year 2000, Period 2 etc.)

I appreciate your time and help in advance!

Period is not a month. Period is duration.

[Anonymous]

Hi @jennratten

Thank you so much for your reply message. Can you please help me automate this? 

How would you create a table that has series of years and period? My table would look like this...

Year (1997~Current)           Period (1~250)

1997                                         1

1997                                         2

1997                                         3

....                                             ....

1997                                        250

1998                                         1

1998                                         2

I appreciate your time and help!!

[KT_Bsmart2gethe · Accepted solution]

Hi @Anonymous ,

Please see below code on how to year and period table:

let
    Source = Table.FromList({1997..Date.Year(DateTime.LocalNow())},Splitter.SplitByNothing(), {"Year"}),
    #"Added Custom" = Table.AddColumn(Source, "Period", each {1..12}),
    #"Expanded Period" = Table.ExpandListColumn(#"Added Custom", "Period")
in
    #"Expanded Period"

Regards

KT

[jennratten]

Hello - you can add a new table with these values and then append the tables together.

Add a new table:

Then append the tables, like so:

= Table.Combine ( { Table1, AdditionalValues } )