Adding missing records in table, extracted from another table

[Satch]

Hi,

I have a dataset, where in 1 original table (Name variant) records were deleted by user.

How can I add the records with information from another table?

Extra question regarding this, see end of post!

More in detail:

I have table orderline and table variant.

Orderline is like

Itemcode	Quantity	Description	costprice	VariantId
A	10	Prod A	1.50	12345
B	8	Prod B	1.35	67890
B	12	Prod B	1.35	67890
C	2	Prod C	2.64	45632

Variant is like

VariantId	Description	Itemcode	Costprice
12345	Prod A	A	1.50
45632	Prod C	C	2.64

How can I add 1 record for prod B in Variant, with the data from orderline.

2nd part of question:

In total I have 4 tables.

Invoiceline, Orderline, Variant, Product

Invoiceline > linked to Orderline > linked to Variant > linked to Product.

So when above question is solved, I have to do the same for Product (Create missingproducts from orderline)

I can manage that.

But then: can I use all the relations and records. Will that work

I mean can I use invoiceline and show f.e. second column = Description form Producttable, in case of the new made records from first part of question?

Hope this part is clear

Any help is appreciated. Thanks in advance

[Anonymous]

Hi @Satch ,

I think it might be easier and faster if you modify it at the data source.

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Yuna

[Anonymous · Accepted solution]

Hi @Satch ,

Based on your description, you can create a new "Variant" table to replace the former one.

New_variant = 

var x1=SUMMARIZE(FILTER('Orderline',[Itemcode]="B"),Orderline[VariantId],Orderline[Description],Orderline[Itemcode],Orderline[costprice])

return

UNION(x1,Variant)

Result:

For Q2, not very clear to me. You can also create a new "Product" table using the same method and Re-establish the relationships between the four tables (mainly the relationships between the Product, Variant and Orderline tables)

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Satch]

Thanks for this approach.

I not behind my PBI desktop now but will test this and let you know.

But how can I do this automatic for all missing records?
Since now it's filtered by Itemode B.

But there's a bunch of recs missing.

Is it possible to do for all missing recs?

Actually you answered Q2 also. what you're telling I was hoping for 🙂

Thanks!