two table comparison to find "OWNER"

[Anonymous]

I have two tables one is Cycle Count data and the other is warehouse owner data. I want to add a new column to the Cycle count table identifying who the owners are of the counts
If WHSE & STYPE from Cycle count table = the WHSE & STYPE from the owners table bring in OWNER BADGE;
if PLANT & SLOC form Cycle count table = PLANT & SLOC from the owners table bring in the OWNER BADGE;
If none match null
I'm spending too much time trying to figure this one out so I'm requesting help

PLANT	INV_TYPE	WHSE	SLOC	STYPE	MATERIAL	DATE	QTY
1000	WM	200	2000	200	35796-7	5/29/2020	68
1000	WM	200	2000	200	69853-4	5/29/2020	7
2000	WM	200	2000	200	75398-4	6/15/2020	14
2000	WM	200	2000	200	98765-4	6/15/2020	9
1000	IM	null	100A		1223-4	4/27/2020	13
1000	IM	null	100A		1357-1	4/27/2020	22
1000	IM	null	100A		95136-6	5/28/2020	18
1000	IM	null	100A		8523-1	5/28/2020	33
1000	IM	null	100A		9632-4	5/29/2020	12
2000	WM	100	2000	200	7536-4	4/27/2020	10
2000	WM	100	2000	200	96431-6	4/27/2020	2
2000	WM	100	2000	200	35789-4	5/28/2020	8
2000	WM	100	2000	200	65498-7	5/28/2020	64
1000	IM	null	100A		195469-7	6/15/2020	7

Plant	Sloc	Whse	Stype	Owner
1000	WM	100	200	BADGE1
1000	WM	100	200	BADGE2
2000	WM	100	200	BADGE3
2000	WM	100	200	BADGE4
1000	100A	IM	IM	BADGE5
2000	100B	IM	IM	BADGE6
1000	100C	IM	IM	BADGE7
2000	100D	IM	IM	BADGE8
1000	100E	IM	IM	BADGE9
1000	100G	IM	IM	BADGE10

[v-juanli-msft]

Hi @Anonymous 

First, Ctrl+Alt+A, select all columns in your table and click on "Transform->Format->trim and clean", this will clean all cells and filter out blank break.

Then i merge query in Table1 as below:

this will complete the first requirement:

If WHSE & STYPE from Cycle count table = the WHSE & STYPE from the owners table bring in OWNER BADGE

But for the same WHSE & STYPE, there are many rows of OWNER BADGE matching, so it would increase more rows to your Table1.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That is only one peice of the formula.. I couldn't get the merge to work because the formula in access is 

If WHSE & STYPE from Cycle count table = the WHSE & STYPE from the owners table bring in OWNER BADGE or
if PLANT & SLOC form Cycle count table = PLANT & SLOC from the owners table bring in the OWNER BADGE else null

This data is linked to our SAP HANA SQL so there shouldn't be any need to clean data at all

[v-juanli-msft]

Hi @Anonymous 

If the method below doesn't solve your problem, could you show me you expected result of example?

->

You could merge query twice and expand the "onwer" column every time, then add a conditional column

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

No that doesn't get me what I need either... but I figured it out on my own 

All I had to do was concatenate all of the data I was looking for in both tables and then do a simple lookup 

Cycle count table = MyKey = CYCLECOUNT[Plant] & CYCLECOUNT[Sloc] & CYCLECOUNT[Whse] & CYCLECOUNT[Stype]

Owners table = MyKey = WHSE_OWNERS[Plant] & WHSE_OWNERS[Sloc] & WHSE_OWNERS[Whse] & WHSE_OWNERS[Stype]

[edhans]

Hi @Anonymous Can you please post that data again using a table format? I am guessing where your columns are and just wasted 5min trying to align the columns, and gave up. See links below. I believe what you want is possible. Please include a sample of your expected result to confirm I am understanding.

Thanks!

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Maybe it will help if I show you how we achieved this in access. There is one file where all of the Owners are identified for each location whither its IM or WM. My second table might be misleading because there is only one owner per location. 

So I linked the table twice once for WM linked by Warehouse/STYPE and then again for IM SLOC/Plant and then used the formula 

Badge: IIf([INV_Type]="WM",[Stockroom_Owners].[Owner_Cbadge],[Stockroom_Owners_1].[owner_cbadge])