Can I create the same relationship on multiple tables in one step in Power Pivot?

[missinglina]

Hi. I have one master table that has all inventory items and their vendor. Then I have many tables which represent specific orders that list varying numbers of items but these tables have no vendor listed and their item name varies from the master list . They have the item's ID number in common. I am creating a relationship between the orders and the master list of items to obtain vendor name, as well as to extract the item name from the master list. The problem I have is that it is very tedious to do it over and over again. All order tables have the exact same structure. Is there a way to tell Power Pivot that the relationship between all of them and the master list is exactly the same? Thanks for any suggestions! I have tried finding the answer but I must be asking it wrong or perhaps applying the wrong tool to begin with within Excel? (2016 version although 2010 can also be utilized)

 

[Matt Allington]

Yu should combine all your orders into a single table before loading to power pivot. Take a look at my series of blog posts for some ideas how to do that. Combine Excel Workbooks with Power Query

 

[missinglina]

Thank you, Matt. I know realize that my order table are not exactly alike in structure b/c the header for the QTY column also happens to contain the order number. So as the order number changes, the header changes. So the column names for QTY all start the same but then change when they get to the order number. This must be why it can't automatically detect relationships? And to your point, I suppose I need bring down the order number into it's own column before I merge all orders onto one table b/c currently there is no other way to tell them apart except for the QTY column's order number. This is the name format of that column: "QTY PO 12345678".

 

[Matt Allington]

If you use a combine technique, are you able to NOT promote rows to header and then manually create your header? You would then have your po number as the first row of every order. If you then read my blog post from last week, I show how to take the first row data and add it as a column (step 2 in the blog) Power Query Over a Command Screen Output File - Excelerator BI