Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hoping that someone may have a suggestion on a table merge question that I'm running into based on how some of our billing is setup.
One table lists the what each of our Invoices contain. They can be from 1 to n rows.
Table1: InvoiceLines
Unit | Amount | Invoice |
10 | $ 10.00 | IR222 |
11 | $ 5.00 | IR222 |
10 | $ 20.00 | IR223 |
5 | $ 50.00 | IR224 |
9 | $ 100.00 | IR224 |
10 | $ 50.00 | IR224 |
We have a second table that lists when a specific invoice may have been sent. One invoice may be sent between 1 and n times (such as in the case of a subscription).
Table2:InvoiceRecords
Amount | Invioce | Date |
$ 15.00 | IR222 | 1/15/2019 |
$ 20.00 | IR223 | 1/20/2019 |
$ 200.00 | IR224 | 1/21/2019 |
$ 15.00 | IR222 | 1/30/2019 |
$ 200.00 | IR224 | 2/1/2019 |
I'm trying to merge the two tables to allow for additional analysis in one of our reports. The end result of merging the two above would show every sent date with every line that was included on the invoice matched off the Invoice.
MergedTable: Hopeful Result
Date | Invoice | Unit | Amount |
1/15/2019 | IR222 | 10 | $ 10.00 |
1/15/2019 | IR222 | 100 | $ 5.00 |
1/20/2019 | IR223 | 10 | $ 20.00 |
1/21/2019 | IR224 | 5 | $ 50.00 |
1/21/2019 | IR224 | 9 | $ 100.00 |
1/21/2019 | IR224 | 10 | $ 50.00 |
1/30/2019 | IR222 | 10 | $ 10.00 |
1/30/2019 | IR222 | 11 | $ 5.00 |
2/1/2019 | IR224 | 5 | $ 20.00 |
2/1/2019 | IR224 | 9 | $ 50.00 |
2/1/2019 | IR224 | 10 | $ 100.00 |
I hope this makes sense. I've tried the merge queries in the query editor but I can't figure out a way to get them to show correctly. Thank you in advance!
Solved! Go to Solution.
I think you can 'Merge as new' on the Invoice. Use Inner Join.
Expand the InvoiceRecords to bring back the Date.
Re-arrange as required.
There's a 100 in the Units of the desired tabled outcome - is that a mistake?
I think you can 'Merge as new' on the Invoice. Use Inner Join.
Expand the InvoiceRecords to bring back the Date.
Re-arrange as required.
There's a 100 in the Units of the desired tabled outcome - is that a mistake?
Thanks @HotChilli I'll give that a shot. And yes, you are correct, the $100 was incorrect when making the sample data. I didn't actually realize that I could expand the columns. I think that was the key part as it was removing the columns I needed when I tried joining them.
I'll try that out and update. Thanks again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |