cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
esantali
Regular Visitor

Verify Data from different tables

Hello,

 

I have the following tables 

 

Table 1

Department Parts inventory (number)Spare parts inventory (number)
Electronics12
Electronics34
Toys56
Toys78

 

Table 2

DepartmentPart
Electronics1
Toys2
Toys7
Electronics6

 

I want to create a column in table 2, that identifies the Table 2[Department] and Table 2 [Part] values of a particular row, and looks in table 1, if for the value identified in the column Table 2[Department], the value of the Table 2 [Part] of that row appears in Table 1 [Parts inventory (number)] or Table 1 [Spare parts inventory (number)] .

 

Thanks

1 ACCEPTED SOLUTION
mah_priya94
Helper I
Helper I

Hi @esantali,
If its okay to modify the Table 1 a bit then perform the following unpivot operation else create reference or duplicate and then perform the following unpivot operation.

mah_priya94_0-1621281191275.png

 

Your table1 will look like this, 

mah_priya94_1-1621281191267.png

 

 

Then perform the merge operation on Table1 and Table 2.

mah_priya94_2-1621281191303.png

 

and then perform the Expand column operation and get the results you require.

mah_priya94_3-1621281191282.png

 


Hope this helps!

View solution in original post

8 REPLIES 8
mah_priya94
Helper I
Helper I

Hi @esantali,
If its okay to modify the Table 1 a bit then perform the following unpivot operation else create reference or duplicate and then perform the following unpivot operation.

mah_priya94_0-1621281191275.png

 

Your table1 will look like this, 

mah_priya94_1-1621281191267.png

 

 

Then perform the merge operation on Table1 and Table 2.

mah_priya94_2-1621281191303.png

 

and then perform the Expand column operation and get the results you require.

mah_priya94_3-1621281191282.png

 


Hope this helps!

View solution in original post

edhans
Super User
Super User

I think you just want to merge here. Go to table 2 and select Merge, then click on Department, then shift-click on Part. That will tell it to look at Department first, then Part.

 

Then add Table 1 to the merge, and again, Department, then shift-click on Part.

 

edhans_0-1621261529854.png

Then click ok, and expand the inventory column. You get this:

edhans_1-1621261601957.png

If that isn't what you want @esantali can you be a bit more specific along with an image of the expected output - something mocked up in Excel?

 

 



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

Hello @edhans,

 

I am trying to get the same result, but revising also the spare part inventory column. I tried using this method, but could not do it with three columns.

@esantali - you are going to need to provide some data. Using 3 columns with a Merge is no problem as long as they are of the same data type. You cannot compare a numerical field to a text field for example, and make sure none of the fields you are connecting are "untyped" or ABC/123 - Any type. You can get unexpected results.

 

So show us more clearly what you mean when you say it doesn't work with 3 columns. I am not aware of any practical limit to the number of columns you can base the merge on. It might cap out at 256 columns, but no one would actually use something like that. 😀



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

Hi @edhans,

 

This is the problem that I am having.

 

esantali_1-1621350136897.png

 

Also, for the following case, I would like to obtain a "yes" if the part number appears in either the Part inventory (number) column or Spare part inventory (number) column of their respective department, and "no" if this does not happens.

esantali_2-1621350404793.png

 

 

 

You don't do the join on 3 columns on the first and 2 on the 2nd. It is waiting for you to select a 3rd on the 2nd table, and there isn't one. It should look like what I showed you earlier. You likely want to be in Table 2 when you do this so it is at the top. I'm not sure what you want the merge to look like if you start from Table 1 as there will be nothing to pull in, but you could do that and just see what records exist vs what does a null @esantali 

edhans_0-1621350433486.png

 



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

Hi @esantali,
If its okay to modify the Table 1 a bit then perform the following unpivot operation else create reference or duplicate and then perform the following unpivot operation.

mah_priya94_0-1621276586432.png

Your table1 will look like this, 

mah_priya94_1-1621276617658.png

 

Then perform the merge operation on Table1 and Table 2.

mah_priya94_2-1621276657402.png

and then perform the Expand column operation and get the results you require.

mah_priya94_3-1621276730883.png


Hope this helps!

 

 

 

@esantali Kindly verify if this solution would give you the required results.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors