Power Query: How to fill in missing dates in inventory with multiple columns?

[Willem2904]

I'm working on combining a number of tables with inventory numbers and dates into a single table using power query.

If this is my table, the code below works:

Date	Count	Item
01.jan​	100​	A
02.jan​	200​	A
02.jan​	250​	B
03.jan​	300​	A
03.jan​	350​	B
06.jan​	600​	A

let
SourceTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], //assumes you have a table in your workbook named "Table1", which is your "SOURCE" table
Types = Table.TransformColumnTypes(SourceTable,{{"Date", type date}, {"Count", Int64.Type}, {"Item", type text}}),
Pivot = Table.Pivot(Types, List.Distinct(Types[Item]), "Item", "Count"), //pivot our item field so we have a unique field for each item

//these steps generate a continuous list of dates from the first date to the last date in your source table
FirstDate = List.First(SourceTable[Date]),
LastDate = List.Last(SourceTable[Date]),
NumberList = {Number.From(FirstDate)..Number.From(LastDate)},
ListToTable = Table.FromList(NumberList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
BackToDate = Table.TransformColumnTypes(ListToTable,{{"Column1", type date}}),
Rename = Table.RenameColumns(BackToDate,{{"Column1", "FullDateList"}}),

//using the list of dates as the "source", left join our pivoted source table
JoinTable1 = Table.NestedJoin(Rename, {"FullDateList"}, Pivot, {"Date"}, "Pivot", JoinKind.LeftOuter),
ExpandTable1 = Table.ExpandTableColumn(JoinTable1,
"Pivot",
Table.ColumnNames(Pivot),
Table.ColumnNames(Pivot)
), //note we don't refer to any column names here or in the next step other then the ones that won't change
FillDownItems = Table.FillDown(ExpandTable1,
List.Difference(Table.ColumnNames(ExpandTable1), {"FullDateList", "Date"})
),
RemoveOldDateField = Table.RemoveColumns(FillDownItems,{"Date"}),
UnpivotBack = Table.UnpivotOtherColumns(RemoveOldDateField, {"FullDateList"}, "Item", "Count")
in
UnpivotBack

As you can see, the data is calculated correctly.

However, what do I need to do if I add another column, e.g. Location to the table?

Date	Count	Item	Location
01.jan​	100​	A	X
02.jan​	200​	A	X
02.jan​	250​	B	Y
03.jan​	300​	A	Y
03.jan​	350​	B	Z
06.jan​	600​	A	Z

I want the result to look like this:

Date	Item	Location	Count
01.jan	A	X	100​
02.jan	A	X	200​
03.jan	A	X	200​
04.jan	A	X	200​
05.jan	A	X	200​
06.jan	A	X	200​
01.jan	A	Y	0​
02.jan	A	Y	0​
03.jan	A	Y	300​
04.jan	A	Y	300​
05.jan	A	Y	300​
06.jan	A	Y	300​
01.jan	A	Z	0​
02.jan	A	Z	0​
03.jan	A	Z	0​
04.jan	A	Z	0​
05.jan	A	Z	0​
06.jan	A	Z	600​
01.jan	B	Y	0​
02.jan	B	Y	250​
03.jan	B	Y	250​
04.jan	B	Y	250​
05.jan	B	Y	250​
06.jan	B	Y	250​
01.jan	B	Z	0​
02.jan	B	Z	0​
03.jan	B	Z	0​
04.jan	B	Z	0​
05.jan	B	Z	0​
06.jan	B	Z	350​

Thanks!

 

[Willem2904]

Solved it!
Had to merge (Concatenate) the two columns (Item & Location) and pivot that column instead.

 

[I LOVE POWER BI]

Dear Willam, I am struggling with a similar problem right now, however I can't fully reconcile your solution above. Especially the step where you expand the merge I get lost somehow. Can you please send the file you used before or explain the steps from "ExpandTable1" in more details? Your support is sooo much appreciated, really struggle here!