Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

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 NameProductsSKUOn OrderOn StockOn TransferDays Since Last SalesCost
100 Park CityAppleCare for Nano/ShuffleACAPAP000002 0  0
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180

 

We have so many duplicates. I want to create a sub table from Invetory like below. 

 

Store NameProductsSKUOn OrderOn StockOn TransferDays Since Last SalesCost
100 Park CityAppleCare for Nano/ShuffleACAPAP000002 0  0
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180

 

Can anybody please tell me best way to achieve this? If that make sense. 

 

Thank you so much for your time and help 

1 ACCEPTED SOLUTION

Hello, 

 

I was able to achieve this with Summarize function.

 

Thank you for your help

View solution in original post

4 REPLIES 4
Drors
Resolver III
Resolver III

You can reference (or duplicate) that table and then select all columns >> right click >> Remove Duplicates..

@dananjayaprasad

 

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

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 NameProductsSKUOn OrderOn StockOn TransferDays Since Last SalesCost
100 Park CityAppleCare for Nano/ShuffleACAPAP000002 0  0
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180

 

We have so many duplicates. I want to create a sub table from Invetory like below. 

 

Store NameProductsSKUOn OrderOn StockOn TransferDays Since Last SalesCost
100 Park CityAppleCare for Nano/ShuffleACAPAP000002 0  0
100 Park CityAppleCare Apple TVACAPAP000003 0 12580
100 Park CityApple Lightning to USB Camera AdapterBAAIAP00000801 720
100 Park CityApple Lightning to SD Card Camera Reader (EOL)BAAIAP000009 0 8140
100 Park CityApple Lightning to VGA AdapterBAAIAP000010 2 13778
100 Park CityApple Lightning to Digital AV AdapterBAAIAP000011 3 6117.8245
100 Park CityApple 12W USB Power AdapterBAAIAP000018012 3180

 

Can anybody please tell me best way to achieve this? If that make sense. 

 

Thank you so much for your time and help 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.