Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
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
dananjayaprasad
Helper I
Helper I

How to create a sub table (like sub query on SQL) in Power BI Desktop?

‎01-05-2018 12:45 PM

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 

Message 1 of 5
9,789 Views
0
1 ACCEPTED SOLUTION
dananjayaprasad
Helper I
Helper I
In response to Zubair_Muhammad

‎01-10-2018 10:59 AM

Hello, 

 

I was able to achieve this with Summarize function.

 

Thank you for your help

View solution in original post

Message 5 of 5
10,615 Views
0
4 REPLIES 4
Drors
Resolver III
Resolver III

‎01-07-2018 05:30 AM

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

Message 3 of 5
9,766 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to Drors

‎01-07-2018 09:58 AM

@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)


Regards
Zubair

Please try my custom visuals
Message 4 of 5
9,758 Views
0
dananjayaprasad
Helper I
Helper I
In response to Zubair_Muhammad

‎01-10-2018 10:59 AM

Hello, 

 

I was able to achieve this with Summarize function.

 

Thank you for your help

Message 5 of 5
10,616 Views
0
dananjayaprasad
Helper I
Helper I

‎01-05-2018 12:32 PM

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 

Message 2 of 5
9,753 Views
0

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.

View All