Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have the following tables
Table 1
Department | Parts inventory (number) | Spare parts inventory (number) |
Electronics | 1 | 2 |
Electronics | 3 | 4 |
Toys | 5 | 6 |
Toys | 7 | 8 |
Table 2
Department | Part |
Electronics | 1 |
Toys | 2 |
Toys | 7 |
Electronics | 6 |
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
Solved! Go to Solution.
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.
Your table1 will look like this,
Then perform the merge operation on Table1 and Table 2.
and then perform the Expand column operation and get the results you require.
Hope this helps!
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.
Your table1 will look like this,
Then perform the merge operation on Table1 and Table 2.
and then perform the Expand column operation and get the results you require.
Hope this helps!
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.
Then click ok, and expand the inventory column. You get this:
If that isn't what you want @Anonymous can you be a bit more specific along with an image of the expected output - something mocked up in Excel?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @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.
@Anonymous - 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. 😀
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans,
This is the problem that I am having.
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.
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 @Anonymous
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous,
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.
Your table1 will look like this,
Then perform the merge operation on Table1 and Table 2.
and then perform the Expand column operation and get the results you require.
Hope this helps!
@Anonymous Kindly verify if this solution would give you the required results.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |