Forum Discussion
Look up existing table to map value - Help understanding the formula
I have been handed an existing document that I need to update and need help to get the formulas working. I have little experience in power query so forgive me if this is basic. The purpose of the document is to create journal lines that can be uploaded into a specific D365 format. Part of that process includes 2 mapping tables that a user can maintain but that are used as lookups to created content in those journal lines.
I have 2 tables
- AccountMappingTable
- CustomerPaymentMethodTableValue
The first table is looking up the account number to map COGS to the correct account and this formula is working for that function
(
if
ComponentFieldsBuffered[#"Creditors_Detailed.Total Due to Supplier"]{
ComponentRowNumber
} = 0
then
Text.From(AccountMappingTable[Full.COGS Acct]{ReceiptComponentRowNumber})
& "-SD00006-100335----"
else
"11320-SD00006-100335----"
),
The second table has the customer rec account that I need to map on the receipt side but the function was left incomplete. I have tried to replicate the above function but I think I am misunderstanding which fields I should be referencing. Is anyone able to help me identify/breakdown what the above formula is doing ? Essentially I want to use the Payment Mode (ReceiptesFields.Payment Mode) to map the correct account from the table below. If the Mode cannot be found then it should show the text N/A
The table is
CustomerReceipts_Detailed.Payment Mode Customer Receipt D365
| Direct Deposit | BANK |
| Paymentgate | REC00012 |
| MINT EFT local | REC00011 |
| Credit Card | REC00013 |
| Fund Transfer | REC00014 |
| Direct to Supplier | REC00015 |
| Direct Debit | REC00016 |
| Credit Card/Master Card | REC00017 |
| Direct To Supplier/Master Card | REC00018 |
Any help would be greatly appreciated or any direction to any learning references would be great aswell
2 Replies
Hi newbie156 ,
Based on your description and the formulas provided, first is an explanation for what the above code does: ComponentFieldsBuffered[#“Creditors_Detailed.Total Due to Supplier”]{ComponentRowNumber}: this part selects a specific value from the ComponentFieldsBuffered table, which is determined by the row number specified by ComponentRowNumber. It is checking if the value in the “Creditors_Detailed.Total Due to Supplier” column is 0.
AccountMappingTable[Full.COGS Acct]{ReceiptComponentRowNumber}: this part looks up the value of the “Full.COGS Acct” column from the AccountMappingTable table using the ReceiptComponentRowNumber row number.
Text.From(...) & “-SD00006-100335----”: if the condition is true, the account number looked up is converted to text and a static string is appended.
else “11320-SD00006-100335----”: if the condition is false, use another static string.
For the second table, you want to use ReceiptesFields.Payment Mode to find the corresponding account in the CustomerPaymentMethodTableValue table. If there are no matches, then “N/A” should be returned. The following are similar formulas that you might use:let // Assuming ReceiptesFields is a table and Payment Mode is a column in that table PaymentMode = ReceiptesFields[Payment Mode], // Lookup the corresponding account in the CustomerPaymentMethodTableValue LookupResult = Table.First(CustomerPaymentMethodTableValue, each _["CustomerReceipts_Detailed.Payment Mode"] = PaymentMode), // Check if the lookup was successful Account = if LookupResult is not null then LookupResult["Customer Receipt D365"] else "N/A" in AccountBest regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Albert
Thanks so much for the help, that really helped me to understand the issue. I am getting an invalid identifier error any suggestions?
