Combine 3 tables into 1 Pivot Table using Data Model based on common Date?

[StrawberryDreams]

As the title suggests I would like to combine 3 different tables into one pivot table.

I have looked at the Data Model and have created Connections and Relationships but it doesn't seem to work. As it will just average values for dates that might not have data in each table. How can I do this ? I also only have excel 2016, so I do not currently have access to power pivot if this would make it easier.

 

[alansidman]

Based upon the information shown, what would your mocked up solution look like?

 

[StrawberryDreams]

Based upon the information shown, what would your mocked up solution look like?

Hi Alan thanks for your response.

basically a simple Pivot table like this is what I'm after :

 

[alansidman]

Using Power Query. Loaded each table to PQ Editor. Joined each on common field Date with a full outer join. Then applied the following Mcode

let
    Source = Table.NestedJoin(Merge1, {"Date"}, Table4, {"Date"}, "Table4", JoinKind.FullOuter),
    #"Expanded Table4" = Table.ExpandTableColumn(Source, "Table4", {"Date", "Color", "weight"}, {"Date.2", "Color", "weight"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table4", "Custom", each if [Date] = null then [Date.1] else [Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = null then [Date.2] else [Custom]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1", "weight", "Color", "Amount", "PO#", "Value", "Product", "Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Other Columns",{"Date"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.1", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Custom.1", "Product", "Value", "PO#", "Amount", "Color", "weight"})
in
    #"Reordered Columns"

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Product	Value			Date	PO#	Amount			Date	Color	weight
2	1-Jan	Hat	20			2-Jan	1234	4			1-Jan	red	30
3	4-Jan	Shirt	50			4-Jan	2345	3			5-Jan	blue	20
4	10-Jan	Pants	80
5						Custom.1	Product	Value	PO#	Amount	Color	weight
6						44927	Hat	20			red	30
7						44928			1234	4
8						44930	Shirt	50	2345	3
9						44931					blue	20
10						44936	Pants	80
Sheet1

 

[Peter_SSs]

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: Combine 3 tables into 1 Pivot Table using Data Model based on common Dates ?
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.

 

[StrawberryDreams]

Hi Alan,

Thank you for the tips. I have never used PQ, so I will look into how to apply what you wrote. By the way, is this method dynamic, in that if I add to any of those three tables the merged PQ table will update ?

 

[StrawberryDreams]

Using Power Query. Loaded each table to PQ Editor. Joined each on common field Date with a full outer join. Then applied the following Mcode

let
    Source = Table.NestedJoin(Merge1, {"Date"}, Table4, {"Date"}, "Table4", JoinKind.FullOuter),
    #"Expanded Table4" = Table.ExpandTableColumn(Source, "Table4", {"Date", "Color", "weight"}, {"Date.2", "Color", "weight"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table4", "Custom", each if [Date] = null then [Date.1] else [Date]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = null then [Date.2] else [Custom]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1", "weight", "Color", "Amount", "PO#", "Value", "Product", "Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Other Columns",{"Date"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.1", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Custom.1", "Product", "Value", "PO#", "Amount", "Color", "weight"})
in
    #"Reordered Columns"

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Product	Value			Date	PO#	Amount			Date	Color	weight
2	1-Jan	Hat	20			2-Jan	1234	4			1-Jan	red	30
3	4-Jan	Shirt	50			4-Jan	2345	3			5-Jan	blue	20
4	10-Jan	Pants	80
5						Custom.1	Product	Value	PO#	Amount	Color	weight
6						44927	Hat	20			red	30
7						44928			1234	4
8						44930	Shirt	50	2345	3
9						44931					blue	20
10						44936	Pants	80
Sheet1

Hi Alan,

I merged Product with Po, then expanded, then merged that with Color to get them all into one table. After that I opened advanced editor and pasted your Mcode and pressed enter and received this error :

Not sure what I might have done wrong.

 

[StrawberryDreams]

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: Combine 3 tables into 1 Pivot Table using Data Model based on common Dates ?
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.

Hi Peter,

Sorry about that. I originally posted here first and wasn't aware about cross posting issues - so thanks. I did post in the other forum since I initially thought I wouldn't get a reply.

Combine 3 tables into 1 Pivot Table using Data Model based on common Dates ?

 

[alansidman]

Not sure what that message means. You may want to google it. Did you name your tables the same as those in the Mcode. It may also be a case of field names that have been renamed not in sync with yours.