Power Query combine two tables to fill in gaps in data

[phbryan]

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 need to add back in the missing points at a value of zero. Example output shown in file.

I need to build a list of every month and every item in a table, that I can then combine to give the total quantity for that month.
I know how to combine, but i am struggling with building the list.

 

[JoePublic]

Cross-posted:

Power Query to Merge and Fill in gaps in data

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...

www.mrexcel.com

Please read this.

 

[Jaco-040]

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 need to add back in the missing points at a value of zero. Example output shown in file.

I need to build a list of every month and every item in a table, that I can then combine to give the total quantity for that month.
I know how to combine, but i am struggling with building the list.

Hi,

Use a table with all the month endings and join that with your sales data. That will bring up every month, also the months without sales.
HTH.

Jaco

 

[p45cal]

Does this not do it:?

let
    Source = Months,
    AddedCustom = Table.AddColumn(Source, "Items", each Items),
    ExpandedItems = Table.ExpandTableColumn(AddedCustom, "Items", {"Item"}, {"Item"}),
    MergedQueries = Table.NestedJoin(ExpandedItems, {"Month Ending", "Item"}, Sales_Data, {"Month Ending", "Item"}, "Sales_Data", JoinKind.LeftOuter),
    ExpandedSales_Data = Table.ExpandTableColumn(MergedQueries, "Sales_Data", {"Sum of Quantity"}, {"Sum of Quantity"}),
    ReplacedValue = Table.ReplaceValue(ExpandedSales_Data,null,0,Replacer.ReplaceValue,{"Sum of Quantity"})
in
    ReplacedValue

In the attached, the new table is next to your table on the Desired output from power query sheet. I can't see any difference except for 30th Nov 2022 Item A which I think is a typo in your expected results.