Forum Discussion
Helper Iadd column to query to include missing items (DAX)
I have a query with all GL line items. Some of them are missing the vendor information. I would like to add a column to the query that takes the Accounting Document ID and adds the vendor to all distinct accounting documents.
Example:
| Accounting Document | Vendor ID | Vendor Name | Amount | GL code |
| 10000 | 2000 | Hill ltd. | 2000 | 20000 |
| 10000 |
| 500 | 75000 | |
| 10001 | 2001 | Light Ltd | 1000 | 20000 |
| 10001 | 2001 | Light Ltd | 300 | 25000 |
| 10001 | 500 | 76000 |
and I would need the new column to look like this:
| Accounting Document | Vendor ID | Vendor Name | Amount | GL code | Vendor added |
| 10000 | 2000 | Hill ltd. | 2000 | 20000 | 2000 |
| 10000 |
| 500 | 75000 | 2000 | |
| 10001 | 2001 | Light Ltd | 1000 | 20000 | 2001 |
| 10001 | 2001 | Light Ltd | 300 | 25000 | 2001 |
| 10001 | 500 | 76000 | 2001 |
How do I do this? Add a column to the query? or add a new table? I don't want to amend the original query, just add to it.
Either way you suggested works. If you choose to add a column, then you can use the following DAX:
Vendor added = CALCULATE(MAX(Table[Vendor ID]), ALLEXCEPT(Table[Accounting Doccument]))
3 Replies
vicky_Super User
Either way you suggested works. If you choose to add a column, then you can use the following DAX:
Vendor added = CALCULATE(MAX(Table[Vendor ID]), ALLEXCEPT(Table[Accounting Doccument]))- Ashish_Mathur
Hi,
In the Query Editor, you may use the Fill Down feature.
PBI-NewbieThanks! vicky_ -I added the company code to the ALLEXCEPT part and it worked like a charm!
