Look up existing table to map value - Help understanding the formula

[newbie156]

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

[newbie156]

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?

[Anonymous]

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
    Account

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly