Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
Sure, pls check this out: https://1drv.ms/u/s!Av_aAl3fXRbehbBF8jyxBFQIy963fQ
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
In the query-editor, you can just hardcode it like this:
Table.Combine({<YourProductTableSoFar>, #table({"Product", "Brand"}, {{"DummyProduct", "DummyBrand"}})})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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!
Sorry, but I don't understand.
Could you please paste dummy-data and a clear description of the desired result?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I tried to explain it in this file: https://drive.google.com/open?id=1Xg3hB80FnWnBXoi8l3LGQjyNZfogeYDc
Hope it makes it clear
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks
But the joining in M gives me headaches... 😞
Can you help me with this? Maybe an example.
Sure, pls check this out: https://1drv.ms/u/s!Av_aAl3fXRbehbBF8jyxBFQIy963fQ
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks so much Imke.
I would never have figured this out myself whit what I have read about joining in M.
Thanks!!
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
To follow up the queries, I need the source data as well. Could you please share them?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |