Missing data in 1 table, how to workaround

[Satch]

Hi, strange situation.

In 1 of the main tables there are rows deleted by the owner in the source.

It's impossible to get these back because we cannot create the right id.

Now we can accept the solution that all records, which don't have a connection in the main table, will be connected to 1 record in the main table. But how to do this?

I'll try to explain the datamodel. Invoicelines is connected to product, brands is also connected to product.

Appearantly C and E from invoicelines can never get to product.

But would be great if those 2 could connect to (a new?) created rec "Empty" in product.

And can be sliced by brand "Empty"

                                         Brand

Product                                1

A                                         2

B                                         3

D

F

                                     Invoicelines

                                        A

                                        B

                                        C

                                        D

                                        E

                                        F

[ImkeF]

1) Create a new column in "Invoiceline" called "ProductIdNew" where you check whether the ProductID is included in "Product" and if not, allocate the DummyProductID there.

2) Add DummyProductID to table "Product" with "DummyBrandID" into the column of "BrandID"

3) Add DummyBrandID to table "Brand"

[Satch]

Hi Imke,

thanks for your reply.

This indeed should solve the missing link thing.

But how do I add 1 record to the producttable or brandtable on the fly.

I mean after refreshing the dataset that record is gone...

Thanks

[ImkeF]

In the query-editor, you can just hardcode it like this:

Table.Combine({<YourProductTableSoFar>, #table({"Product", "Brand"}, {{"DummyProduct", "DummyBrand"}})})

[Satch]

@ImkeF

Hi Imke, finally found the time to try this. But I ran into a problem.

When I add a Column and check if related producttable has id, I can get the Id in the new Column, or put a dummy Id there.

But then there must be a relation between Invoice and Producttable.

And so I cannot create a new relationship between Invoice DummyId and ProductId.

So I guess I need to do this in Query stage already, in M?

Can you pls. guide me through that?

M and finding a record in another table is a bit difficult for me.

Thanks in advance!

[ImkeF]

Sorry, but I don't understand.

Could you please paste dummy-data and a clear description of the desired result?

[Satch]

@ImkeF

I tried to explain it in this file: https://drive.google.com/open?id=1Xg3hB80FnWnBXoi8l3LGQjyNZfogeYDc

Hope it makes it clear

[ImkeF]

Yes, the instructions I've given you are in M and need to be done in the query editor.

If you load the tables to your data model, you take the newly created column to connect your tables there.

[Satch]

Thanks

But the joining in M gives me headaches... 😞

Can you help me with this? Maybe an example.

[ImkeF · Accepted solution]

Sure, pls check this out: https://1drv.ms/u/s!Av_aAl3fXRbehbBF8jyxBFQIy963fQ

[Satch]

Thanks so much Imke.

I would never have figured this out myself whit what I have read about joining in M.

Thanks!!

[Satch]

@ImkeF

Hi Imke,

I tried what you did in your  example, which was very clear.

However, in my case the join is not working (it returns all null values)

It must be a tiny detail, but I don't get it.

Would you please look?

See example: https://drive.google.com/open?id=1cim8dqHU6M57YRxsY89lYBc3hdSjeNiy

[ImkeF]

To follow up the queries, I need the source data as well. Could you please share them?

[Satch]

@ImkeF

PM Sent