add dynamic row values from another table

[Anonymous]

Sorry if my post is duplicated. I had searched the issue and found nowhere. Let me know if i can do this way or not. If I can do then, can you please me on this.

I have 2 tables:
Table1- 

Table2- 

 
I want to create a new table using power query line below. Let me know if I can create by any other method also.

The values in each row should be dynamically calculated for each row*column. Thanks in advance.

[Jimmy801]

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

[Anonymous]

Hi @Jimmy801 ,

I am trying to run the query and get the below error.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

change the AddedYear-step as follows

	AddedYear = Table.AddColumn
	(
		ChangeType,
		"Custom",
		each Date.Year
		(
		    [Date]
		)
	),

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

[Anonymous]

Hi @Jimmy801 ,

You are too good. If you dont mind, can you explain me from getQuantityList so that I can make few changes ? 
Please...

[Jimmy801]

Hello @Anonymous 

thank you 😁

what kind a changes you want to make?

This function defines which columns are unpivoted in the next step. The List.Select Function does a filtering of all column names and in the current setting it searches for items where quantitiy is a part of the item.

So the definition happens exactly here

Hope it helps

Jimmy

[Anonymous]

Hi @Jimmy801 ,

I just have calculated column NetSales1 and NetSales2 as shown in below fig.


I have calculated this using a lookup table
NetSales1 = Quantity1 * Rate1 

Using Power Query, I am not able to get the NetSales1 as a row as shown below. Please advise on this.

[Jimmy801]

Hello @Anonymous 

just substitute the GetQuantityList with this code

	GetQuantityList = List.Select
	(
		Table.ColumnNames
		(
			Combine
		),
		each Text.Contains
		(
			Text.Lower
			(
				_
			),
			"quantity"
		) or 
        Text.Contains
		(
			Text.Lower
			(
				_
			),
			"netsales"
		)
	),

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

[Anonymous]

NetSales row is not shown in the table.  😕


NetSales is a calculated column which is calculated by dax formula.

[Jimmy801]

Hello @Anonymous 

if netsales is part of your data, then is there. If you are adding this information only on Power BI as additional DAX column, then there is no way to have it here in Power Query. Then you need to add this information as new column in Power Query instead

BR

Jimmy

[Anonymous]

Hey @Jimmy801 ,

Yes the netSales is calculated in power bi. Is there a way where i can calculate the column in power query.
Can you give me a ref link or example where i can calculate from lookup table and use conditions in it ?

[Jimmy801]

Hello @Anonymous 

the general microsoft site is a good starting point link.

In your case check out the Table.NestedJoin-function. This function enables relationships between 2 tables. In case you have question, post a new question to the forum.

All the best

Jimmy