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
Atinder
Helper III
Helper III

pull data from different tables into new table

Hello. I am trying to pull data from data from 2 different tables into table. 

 

1st table :  ON HAND inventory which included all sales and purchase receipts. returns . gives me real on hand qty. I get my on hand after I sum all the transcations.

 

2nd table with just sales last 90D sales. monthly average (90d sales/3) with measure.

 

I want to build a table with 3 columns

1. items

2. on hand qty 

3. sales Average 

 

I am only only able to pull items with Summerize formula. How can I pull on final on hand qty by an item which will be sum of all tranctions in the 1st table  and Average by an item from second table?

 

 

Thank you

 

 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Atinder 

 

If both tables have the Item column, you can first group by "Item" column in the first table and add a new column by performing Sum aggregation on "On Hand Qty" column. Then use Merge Queries to merge the second table to the first table based on "Item" column. You will see a new table column. Click the icon on its column header and expand only "Sales average". Select "Sales average" column and divide it by 3 from Transform tab > Number column > Standard > Divide

 

Hope this helps. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

edhans
Super User
Super User

I would not do this. You need 3 tables in your model:

  • Your Product Table - this is a DIM table. It will have all product IDs in it as a unique field, along with any other dimensions - description, vendor, category, etc.
  • Your On Hand table
  • Your Sales table

Now, drop the item number from your Product table in a visual, and measures from both On Hand/Sales tables (those are FACT tables) in to the visual and you will get the desired results.

 

If you honestly need a table that is not going ot be used by Power BI, but you are using Power Query for excel and dropping data into the spreadsheet layer, we need sample data - good sample data with expected output because the M code will not be pretty and will not be done via the UI.

 

But this is not a good modeling technique. The Star Schema approach I mentioned at the top is the way to go for Power BI. 

Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Importance of Star Schema

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors