Power Query to Merge and Fill in gaps in data

[phbryan]

Hello,

I have monthly sales data by item for 12 months. Some items do not have any sales data in every month. My raw data source omits these months and skips over them. However, I need to show these months/item as ZERO for that month.
Some conditions that are always true:

1. For any month in the 12 months, there will always be some items sold (no month will be zero across the board).
2. Each item should have a data point for each of the 12 months (some months will have to be filled in at zero).

I have attached some screenshots of my saw data and desired output. The rows in yellow would be created by power query.

My initial thoughts would be to create two new tables and combine. First table being the list of months and the second being the list of items and create a merged table of months and items to then lookup the quantities in the original dataset, returning zero if no quantity exists. Just not sure now to go about it.

 

[ExcelToDAX]

Without knowing how you originally got the input data, I am assuming you have the last month of actuals and then the previous 12 months. You can create this separate table that has End of Month and Item columns:

let
    Dates = List.Generate(
  () =>     Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),-12), // Starting value
  each _ <= Date.AddDays(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),-1), // Last Month of Actuals
  each Date.AddMonths( _, 1 ) // Take steps of 1 month
),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted End of Month" = Table.AddColumn(#"Converted to Table", "End of Month", each Date.EndOfMonth([Column1]), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted End of Month",{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each {"A","B","C"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Custom", Order.Ascending}, {"End of Month", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"End of Month", "Month Ending"}, {"Custom", "Item"}})
in
    #"Renamed Columns"

and then you can join this table with your other table and replace the null values with 0

 

[AlienSx]

@phbryan: you need just one more table with blank sales but with all dates and items. Then combine it with your Input table and group by date and item name.

    // create a table with all combinations of dates and items
    all_blank_sales = Table.FromList(
        List.Distinct(Input[Month Ending]), 
        (x) => {x} & {List.Distinct(Input[Item])}, 
        Value.Type(Input)
    ),
    // expand column with items and combine it with Input table
    combine_sales = Table.ExpandListColumn(all_blank_sales, "Item") & Input, 
    // group by date and item name to get total sales
    group = Table.Group(
        combine_sales, 
        {"Month Ending", "Item"}, 
        {"Sum of Quantity", (x) => List.Sum(x[Sum of Quantity])}
    )

 

[phbryan]

Without knowing how you originally got the input data, I am assuming you have the last month of actuals and then the previous 12 months. You can create this separate table that has End of Month and Item columns:

let
    Dates = List.Generate(
  () =>     Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),-12), // Starting value
  each _ <= Date.AddDays(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),-1), // Last Month of Actuals
  each Date.AddMonths( _, 1 ) // Take steps of 1 month
),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted End of Month" = Table.AddColumn(#"Converted to Table", "End of Month", each Date.EndOfMonth([Column1]), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted End of Month",{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each {"A","B","C"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Custom", Order.Ascending}, {"End of Month", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"End of Month", "Month Ending"}, {"Custom", "Item"}})
in
    #"Renamed Columns"

and then you can join this table with your other table and replace the null values with 0

Hi ExcelToDax,

I am able make your solution work for my example, but I am having trouble implementing. See comments Below:

Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),-12), // Starting value

- Can this be dynamic based on the months present? (6 months, 12 months, 24 months, etc)

#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each {"A","B","C"}),

- Can this be dynamic? my list can be thousands of different items, with some dropping off/new items as I add new sales data.

 

[phbryan]

@phbryan: you need just one more table with blank sales but with all dates and items. Then combine it with your Input table and group by date and item name.

    // create a table with all combinations of dates and items
    all_blank_sales = Table.FromList(
        List.Distinct(Input[Month Ending]),
        (x) => {x} & {List.Distinct(Input[Item])},
        Value.Type(Input)
    ),
    // expand column with items and combine it with Input table
    combine_sales = Table.ExpandListColumn(all_blank_sales, "Item") & Input,
    // group by date and item name to get total sales
    group = Table.Group(
        combine_sales,
        {"Month Ending", "Item"},
        {"Sum of Quantity", (x) => List.Sum(x[Sum of Quantity])}
    )

Hi AlienSx,

I've gotten this far with your code. I=My actual sheet has 24 months:

let
    #"Sales_Data (2)" = let
  // create a table with all combinations of dates and items
    all_blank_sales = Table.FromList(
        List.Distinct(Input[Month Ending]), 
        (x) => {x} & {List.Distinct(Input[Item])}, 
        Value.Type(Input)
    ),
    // expand column with items and combine it with Input table
    combine_sales = Table.ExpandListColumn(all_blank_sales, "Item") & Input, 
    // group by date and item name to get total sales
    group = Table.Group(
        combine_sales, 
        {"Month Ending", "Item"}, 
        {"Sum of Quantity", (x) => List.Sum(x[Sum of Quantity])}
    )
in
all_blank_sales
in
    #"Sales_Data (2)"

Not sure how to proceed.

 

[RoryA]

@phbryan
A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Power Query combine two tables to fill in gaps in data

Hello, In the attached spreadsheet, I have a list of monthly sales data for three items (24 months / 3 items) For items A and B, each item has sold in each of the 24 months. For item C, it has only sold during 7 out of the 24 months. My raw data omits months where there are now data points. I...

forums.excelguru.ca

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[AlienSx]

let
  // create a table with all combinations of dates and items
    all_blank_sales = Table.FromList(
        List.Distinct(Input[Month Ending]),
        (x) => {x} & {List.Distinct(Input[Item])},
        Value.Type(Input)
    ),
    // expand column with items and combine it with Input table
    combine_sales = Table.ExpandListColumn(all_blank_sales, "Item") & Input,
    // group by date and item name to get total sales
    group = Table.Group(
        combine_sales,
        {"Month Ending", "Item"},
        {"Sum of Quantity", (x) => List.Sum(x[Sum of Quantity])}
    )
in
  group

simply copy and paste this code into blank query. Don't add anything - neither additional "let ... in" nor #"Sales_Data (2)" .

 

[alex78]

an attempt.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cols = Table.ColumnNames(Source),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{cols{0}, type date}}),
    Dates = let x = let x = Table.Column(#"Changed Type", cols{0}) in {List.First(x), List.Last(x)} 
            in List.Distinct(List.Transform(List.Dates(x{0}, Duration.Days(x{1}-x{0}),#duration(1,0,0,0)), Date.EndOfMonth)),
    Rec = let x = Table.Group(#"Changed Type", cols{1}, {{"Rec", 
              each let x= Table.ToColumns(Table.TransformColumnTypes(Table.RemoveColumns(_, cols{1}), {{cols{0}, type text}, {cols{2}, type text}})) 
                   in  Record.FromList(x{1},x{0})}}) 
          in Table.AddColumn(x, "x", 
              each let y = [Rec] 
                   in Table.RenameColumns(Table.AddColumn(Table.FromColumns({Dates}), cols{2}, 
                       each Record.FieldOrDefault(y, Text.From ([Column1]),0)), {{"Column1", cols{0} }})),
    Result = Table.SelectColumns(Table.ExpandTableColumn(Rec, "x", List.RemoveItems(cols,{cols{1}})), cols)
in
    Result

Regards,