Hi there,
I'm quite new to PowerQuery and PowerPivot and at the moment following Myndas fantastic online course, so I guess this is a simple question for most of you:
In the attached file, the situation is:
I have one query (InvoiceLine) containing invoice No, invoice line and article No and another query (Invoice) containing invoice No (unique key) and invoice date. I have created a relation from InvoiceLine/Invoice No to Invoice/Invoice No.
In the pivot table, I took invoice No and article No from InvoiceLine and invoice date from Invoice. There is no warning that relationships should be created, but the result is that for each invoice No I get all the dates from Invoice.
I have created a lot of pivot tables based on other queries in my master file without any problems and I simply cannot figure out what I am doing wrong in this case.
Can anybody help me, please?
Thanks you so much in advance,
Marianne
Can you attach the file you mentioned?
Hi Catalin
Ahhh, forgot to press "start upload". Hope the file is attached now 🙂
Hi Catalin
Forgot to mention, that in the meantime I created a merge in PQ which gave me the result I wanted - and maybe this is also a better way in general. But I'm still curious to know what is wrong with my data in the attached file.
Marianne
Hi Marianne,
You get all the dates from the 'Invoice' table because you've put that field in your PivotTable but what you really should do in this case is add a column to your InvoiceLine table using the RELATED funtion to bring the Invoice Date into that table. Then in your PivotTable use the 'InvoiceDate' field from the 'InvoiceLine' table.
BTW, it's a good idea to give your InvoiceDate columns different names so you don't get confused.
Let me know if you get stuck.
Mynda
Hi Mynda
Thank you for the quick reply. I solved my problem with a merge between the two queries and that got me the result I wanted.
BR
Marianne
Hi Marianne,
yes, this may be another way to solve it. What Mynda says, is that you should add a new column in Power Pivot Invoice Line table, with a simple formula:
=RELATED(Invoice[InvoiceDate])
This will bring the date from the existing related table - invoice.
See the attached version for an example based on your file.
Cheers,
Catalin
