Power BI combine a unique column in a new table from multiple tables

[jlkrawcyk]

Hi,

I am creating a matrix with multiple measurements from 5 different tables and the data does not seem to tie in correctly. I changed the relationships multiple times, but nothing seems to work.

Example: Specifically looking at this part (PN-514081) and SPAN you can see data only in Jan and Feb.

 HOWEVER in the Data there are more reporting periods:

I have created a unique column  "key" like shown above in each of my tables:

I consider myself a beginner with creating dashboards and my only guess one how to fix this is to create a new table and pull in all the unique key columns into 1 columnm remove any diplicates and link from those? 

I am not even sure that would work. 

Does anyone have any ideas?

Thank you!

Jordan

[jlkrawcyk]

Thanks Edhans,

So far I have created a Master Key using the Union function to combine all similar values between each of the tables.

I had to create an additional column in each table, "reporting period" and set it to the beginning of the month. Reporting Period is included in my key as well.

The data seems to be coorporating now. Thanks for you help and suggestion. I am going to request that I get this book. 

I am pulled into a lot of project with little resources, and am relying to youtube and google in most cases. I really appreciate our guidance!

[edhans · Accepted solution]

You need to show us your model, but I am pretty sure it isn't a Star Schema - 
Microsoft Guidance on Importance of Star Schema

What you need is a Date Table - Creating a Dynamic Date Table in Power Query - and those dates are what become the columns in your Matrix. It would be a 1:Many relationship to the date field in your FACT table - the one with all of that data.

Every key field you want to report on needs to be from a DIM (Dimension) table - Date, Vendor, Location, Product, etc. Those then all are 1:Many to the various Fact tables you have.

For example, you have Receipts and Open Orders. To look at those by date, your Date table would relate to the Receipt date in the Receipt table, then the ORder date (or ship date or whatever) in the Order table, then you put the Date from the Date table in your visual (or any field in the date table - Month, Quarter, Year, whatever) and then the measures/values from both of those fact tables - Receipt amount, Order Amount. 

It is impossible to overstate the importance of Star Schemas in Power BI. I would highly advise a good beginner book on Power BI - like Supercharge Power BI by @MattAllington 

Trust me - 2-3hrs with this book or a similar great resource will save you dozens of hours of frustration.