cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

two table comparison to find "OWNER"

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

PLANTINV_TYPEWHSESLOCSTYPEMATERIALDATEQTY
1000WM200200020035796-75/29/202068
1000WM200200020069853-45/29/20207
2000WM200200020075398-46/15/202014
2000WM200200020098765-46/15/20209
1000IMnull100A 1223-44/27/202013
1000 IMnull100A 1357-14/27/202022
1000IMnull100A 95136-65/28/202018
1000IMnull100A 8523-15/28/202033
1000IMnull100A 9632-45/29/202012
2000 WM10020002007536-44/27/202010
2000WM100200020096431-64/27/20202
2000WM100200020035789-45/28/20208
2000WM100200020065498-75/28/202064
1000IMnull100A 195469-76/15/20207

 

 

PlantSlocWhseStypeOwner
1000WM        100200BADGE1
1000WM        100200BADGE2
2000WM        100200BADGE3
2000WM        100200BADGE4
1000100AIM        IM        BADGE5
2000100BIM        IM        BADGE6
1000100CIM        IM        BADGE7
2000100DIM        IM        BADGE8
1000100EIM        IM        BADGE9
1000100GIM        IM        BADGE10

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: two table comparison to find "OWNER"

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]

View solution in original post

6 REPLIES 6
Highlighted
Super User VI
Super User VI

Re: two table comparison to find "OWNER"

Hi @C37348 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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Community Support
Community Support

Re: two table comparison to find "OWNER"

Hi @C37348 

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.

Capture5.JPG

Capture6.JPG

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.

Highlighted
Frequent Visitor

Re: two table comparison to find "OWNER"

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

Highlighted
Frequent Visitor

Re: two table comparison to find "OWNER"

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])

 

 

access.JPG

Highlighted
Community Support
Community Support

Re: two table comparison to find "OWNER"

Hi @C37348 

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

Capture3.JPGCapture4.JPGCapture5.JPG

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.

Highlighted
Frequent Visitor

Re: two table comparison to find "OWNER"

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]

View solution in original post

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

August Community Highlights

Check out a full recap of the month!

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors