Normalize data contents of two tables into one new table

[ModernAchilles]

Hi,

Any advice on how I can Normalize these two tables into one? I am having a tough time getting this data manipulated in Power Bi Query.

I have these two data source tables. I want to Normalize them into one table. What would the best way to do this?

Would it be best to use a Calculated Table? Filter?

Ideally, I want one table that returns the most recent Product Name, Summed Remaining Inventory, Most Recent Cost Per Unit, Most Recent Retail Price.

Table 1 : Product Data

Product data Example #1

Table 2 : Inventory Data

I am running into the Product having same but different name and Id's too… For some of the Products. Not sure the best way to filter all this.

Table 1 : Product Data

Product data Example #2

 

Table 2 : Inventory Data

Would I sort by Latest product name and id first? Then, grab the Current Inventory, Cost of Product, and Retail Price in a Filter or Calculated table?

I added in two Calculated Columns for MAXDATE and Latest into the Products Data Table. I just can't combine the two tables effectively that returns the correct most recent updated data.

Normally, in Excel I would use Pivot tables and Vlookup, but I am excited about the automated capabilities of Power Bi!

I am new to Power Bi and at DAX too, but need some advice on the best way to create my data model with this data source.

Thanks for all the help!

[v-yuta-msft · Accepted solution]

Hi ModernAchilles,

Merge the two tables based on column created. Suppose the merged table named 'Merge', then create four measures like pattern below:

most recent Product Name =
CALCULATE (
    MAX ( Merge[Product Name] ),
    FILTER ( Merge, Merge[created] = MIN ( Merge[created] ) )
)

most recent Summed Remaining Inventory =
CALCULATE (
 MAX ( Merge[Summed Remaining Inventory] ),
 FILTER ( Merge, Merge[created] = MIN ( Merge[created] ) )
)

most recent Cost Per Unit =
CALCULATE (
 MAX ( Merge[Cost Per Unit] ),
 FILTER ( Merge, Merge[created] = MIN ( Merge[created] ) )
)

most recent Retail Price =
CALCULATE (
 MAX ( Merge[Retail Price] ),
 FILTER ( Merge, Merge[created] = MIN ( Merge[created] ) )
)

Regards,

Jimmy Tao

[ModernAchilles]

Hi Jimmy,

Thanks for the help! Your advice on merging the tables really helped me solve it.

I Used the Product Name and ID with a Left Outer on the inventory table to create the new table. Where I can now pull the right information like you suggested in those formulas.

Thanks again!