add dynamic row values from another table

Hello Anonymous 

this involves quite a few transformation steps

First Combine both tables, then get a list of column names that have to be unpivoted (criteria was contains "quantity"). Apply an Unpivot of quantity-columns, add a new column with the year, delete all not needed columns finally pivot the table again.

Here the complete solutuion

let
	Table1 =
	let
		Source = #table 
		(
			{"Region","Territory","Date","Brand","Quantity1","Quantity2"},
			{ {"Central","A","Jan 2019","Nike","14","10"}, {"West","D","Feb 2019","Nike","5","54"} } 
		)
	 in 
		Source,
	Table2 =
		let
			Source = #table 
			(
				{"Region","Territory","Date","Brand","Quantity3"},
				{ {"Central","A","Sep 2020","Nike","12"}, {"North East","B","Feb 2019","Nike","11"} } 
			)
		 in 
			Source,
	Combine = Table.Combine
	(
		{Table1,Table2}
	),

	GetQuantityList = List.Select
	(
		Table.ColumnNames
		(
			Combine
		),
		each Text.Contains
		(
			Text.Lower
			(
				_
			),
			"quantity"
		)
	),
	Unpivot = Table.Unpivot 
	(
		Combine,
		GetQuantityList,
		"Quantity",
		"Value" 
	),
	ChangeType = Table.TransformColumnTypes
	(
		Unpivot,
		{{"Value", type number}}
	),
	AddedYear = Table.AddColumn
	(
		ChangeType,
		"Custom",
		each Date.Year
		(
			Date.FromText
			(
				[Date]
			)
		)
	),
	RemoveOtherColumns = Table.SelectColumns
	(
		AddedYear,
		{"Custom", "Value", "Quantity"}
	),
	PivotColumn = Table.Pivot
	(
		Table.TransformColumnTypes
		(
			RemoveOtherColumns,
			{{"Custom", type text}},
			"de-DE"
		),
		List.Distinct
		(
			Table.TransformColumnTypes
			(
				RemoveOtherColumns,
				{{"Custom", type text}},
				"de-DE"
			)
		[Custom]
		),
		"Custom",
		"Value",
		List.Sum
	)
 in 
	PivotColumn

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy