Forum Discussion
Solved
How to create a sub table (like sub query on SQL) in Power BI Desktop?
Hello,
I have an inventory table that contains store names, Product, Skus, On transfer quantity, On hand quantity and on order quantity etc. I am trying to join this table with other, but it will not allow me because of the duplicates (Ones of the powerBI member told me to use store name + sku it did not work either due to same reason.
I figure out what is wrong with it, but I have no idea how make a sub table from inventory. Please see below example.
| Store Name | Products | SKU | On Order | On Stock | On Transfer | Days Since Last Sales | Cost |
| 100 Park City | AppleCare for Nano/Shuffle | ACAPAP000002 | 0 | 0 | |||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to VGA Adapter | BAAIAP000010 | 2 | 137 | 78 | ||
| 100 Park City | Apple Lightning to VGA Adapter | BAAIAP000010 | 2 | 137 | 78 | ||
| 100 Park City | Apple Lightning to VGA Adapter | BAAIAP000010 | 2 | 137 | 78 | ||
| 100 Park City | Apple Lightning to VGA Adapter | BAAIAP000010 | 2 | 137 | 78 | ||
| 100 Park City | Apple Lightning to VGA Adapter | BAAIAP000010 | 2 | 137 | 78 | ||
| 100 Park City | Apple Lightning to VGA Adapter | BAAIAP000010 | 2 | 137 | 78 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple 12W USB Power Adapter | BAAIAP000018 | 0 | 12 | 3 | 180 | |
| 100 Park City | Apple 12W USB Power Adapter | BAAIAP000018 | 0 | 12 | 3 | 180 | |
| 100 Park City | Apple 12W USB Power Adapter | BAAIAP000018 | 0 | 12 | 3 | 180 | |
| 100 Park City | Apple 12W USB Power Adapter | BAAIAP000018 | 0 | 12 | 3 | 180 | |
| 100 Park City | Apple 12W USB Power Adapter | BAAIAP000018 | 0 | 12 | 3 | 180 | |
| 100 Park City | Apple 12W USB Power Adapter | BAAIAP000018 | 0 | 12 | 3 | 180 | |
| 100 Park City | Apple 12W USB Power Adapter | BAAIAP000018 | 0 | 12 | 3 | 180 |
We have so many duplicates. I want to create a sub table from Invetory like below.
| Store Name | Products | SKU | On Order | On Stock | On Transfer | Days Since Last Sales | Cost |
| 100 Park City | AppleCare for Nano/Shuffle | ACAPAP000002 | 0 | 0 | |||
| 100 Park City | AppleCare Apple TV | ACAPAP000003 | 0 | 1258 | 0 | ||
| 100 Park City | Apple Lightning to USB Camera Adapter | BAAIAP000008 | 0 | 1 | 7 | 20 | |
| 100 Park City | Apple Lightning to SD Card Camera Reader (EOL) | BAAIAP000009 | 0 | 814 | 0 | ||
| 100 Park City | Apple Lightning to VGA Adapter | BAAIAP000010 | 2 | 137 | 78 | ||
| 100 Park City | Apple Lightning to Digital AV Adapter | BAAIAP000011 | 3 | 6 | 117.8245 | ||
| 100 Park City | Apple 12W USB Power Adapter | BAAIAP000018 | 0 | 12 | 3 | 180 |
Can anybody please tell me best way to achieve this? If that make sense.
Thank you so much for your time and help
Hello,
I was able to achieve this with Summarize function.
Thank you for your help
4 Replies
Hello,
I have an inventory table that contains store names, Product, Skus, On transfer quantity, On hand quantity and on order quantity etc. I am trying to join this table with other, but it will not allow me because of the duplicates (Ones of the powerBI member told me to use store name + sku it did not work either due to same reason.
I figure out what is wrong with it, but I have no idea how make a sub table from inventory. Please see below example.
Store Name Products SKU On Order On Stock On Transfer Days Since Last Sales Cost 100 Park City AppleCare for Nano/Shuffle ACAPAP000002 0 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to VGA Adapter BAAIAP000010 2 137 78 100 Park City Apple Lightning to VGA Adapter BAAIAP000010 2 137 78 100 Park City Apple Lightning to VGA Adapter BAAIAP000010 2 137 78 100 Park City Apple Lightning to VGA Adapter BAAIAP000010 2 137 78 100 Park City Apple Lightning to VGA Adapter BAAIAP000010 2 137 78 100 Park City Apple Lightning to VGA Adapter BAAIAP000010 2 137 78 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple 12W USB Power Adapter BAAIAP000018 0 12 3 180 100 Park City Apple 12W USB Power Adapter BAAIAP000018 0 12 3 180 100 Park City Apple 12W USB Power Adapter BAAIAP000018 0 12 3 180 100 Park City Apple 12W USB Power Adapter BAAIAP000018 0 12 3 180 100 Park City Apple 12W USB Power Adapter BAAIAP000018 0 12 3 180 100 Park City Apple 12W USB Power Adapter BAAIAP000018 0 12 3 180 100 Park City Apple 12W USB Power Adapter BAAIAP000018 0 12 3 180 We have so many duplicates. I want to create a sub table from Invetory like below.
Store Name Products SKU On Order On Stock On Transfer Days Since Last Sales Cost 100 Park City AppleCare for Nano/Shuffle ACAPAP000002 0 0 100 Park City AppleCare Apple TV ACAPAP000003 0 1258 0 100 Park City Apple Lightning to USB Camera Adapter BAAIAP000008 0 1 7 20 100 Park City Apple Lightning to SD Card Camera Reader (EOL) BAAIAP000009 0 814 0 100 Park City Apple Lightning to VGA Adapter BAAIAP000010 2 137 78 100 Park City Apple Lightning to Digital AV Adapter BAAIAP000011 3 6 117.8245 100 Park City Apple 12W USB Power Adapter BAAIAP000018 0 12 3 180 Can anybody please tell me best way to achieve this? If that make sense.
Thank you so much for your time and help
You can reference (or duplicate) that table and then select all columns >> right click >> Remove Duplicates..
You can do it with DAX as well by creating a new Calculated Table
Go to Modelling Tab>>> NEW TABLE
New Table= Distinct(TableName)
Hello,
I was able to achieve this with Summarize function.
Thank you for your help
