Forum Discussion
Calculating multiple columns from two different tables
Hi CA8172
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
I have a solution as below:
Assume your data is like
In Edit queries,
1. unpivot other columns for "purchased" column, the same for "sold"
2. Rename columns, then groupby columns
The same for " Sold" table(Table 2)
3. Then merge two tables, expand value
Finally, close&&apply, create a measure
Inventory = SUM('Table 1'[purchased value new])-SUM('Table 1'[Table 2.sold Value])Best Regards
MaggieCommunity 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.
In Power Query:
Transform both matrices into tables. So for both tables...
- Raname Purchased / Sold headers for your first column with Product
- Highlight Stores 1 - 6 columns and under Transform, select Unpivot Columns
- It'll convert it into two columns, essentially Store and Purchased / Sold amounts once you rename the headers
Then you'll want to merge in your Sold table into your purchased table so...
- While in your Purchased table, go to Home > Merge Queries dropdown > Merge Queries as new
- Highlight both Product and Store columns.
- Select your Sold table for the merge and highlight the same columns.
Lastly create a new column for Inventory which is Purchased - Sold
It looks like I was not aware that there were some duplicate Products in each of the tables and when I went to apply the appended queries, etc. I got the below error message. Is there any other way around this OR do we need to rename some of the duplicate Products so they can be identified differently?
Apply query changes
Merge1
Load was cancelled by an error in loading a previous table.
Purchased
Column 'Products' in Table 'Purchased' contains a duplicate value 'Z001-702' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
Sold
Column 'Products' in Table 'Sold' contains a duplicate value 'Z001-702' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
It shouldn't break the merge. It sounds like an issue with the model. Likely you have a one to many relationship with a third table and a duplicate record was added in since the data was last loaded in making it no longer a one to many but a many to many.
Idk if that is actually the same product or not, but either way you should probably try to fix it so the data is accurate otherwise it'll still be an issue or cause inaccuracies.
If they're the same, grouping by Product will do the trick. If not, you'll want to rename it or use a different Unique ID.
PANDAmonium- Should I remove any & all Relationships prior to creating the Unpivoted Columns?