cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Satch
Helper III
Helper III

Adding missing records in table, extracted from another table

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

 

ItemcodeQuantityDescriptioncostpriceVariantId

A

10Prod A

1.50

12345
B8Prod B1.3567890
B12Prod B1.3567890
C2Prod C2.6445632

 

Variant is like

VariantIdDescriptionItemcodeCostprice
12345Prod AA1.50
45632Prod CC2.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

1 ACCEPTED SOLUTION
v-yuaj-msft
Community Support
Community Support

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:

v-yuaj-msft_0-1614929755831.png

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.

View solution in original post

3 REPLIES 3
v-yuaj-msft
Community Support
Community Support

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

v-yuaj-msft
Community Support
Community Support

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:

v-yuaj-msft_0-1614929755831.png

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.

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!

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!