How to append a value to results from a lookup table

[IsmailK]

I have a search that return the no. of transactions for each individual http result code and maps them to the respective result code name, like "2xx, 3xx maps to "Successful", 4xx maps to "Auth Failures" and 5xx maps to "Server Error"" in a lookup table.  I then use Power BI as the reporting tool via Splunk ODBC. 

Power BI expects to have all three result code names in the search report every time, so if for instance, there are no failures for that period, then "Server Error" will not be in the search report. Which means that the Power BI report refresh fails. So I need to be able to have all 3 result code names returned regardless, and just fill in 0 as the number of transactions

Part of my search shown below

index search

| lookup AppResultCode ResultCode as TransactionStatus OUTPUT Status
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus sum(PercentTransactions) as PercentTransactions by Status
| table Status NoOfTransactionsPerStatus PercentTransactions

Status	NoOfTransactionsPerStatus	PercentTransactions
Authentication Failure	205	0.40
Successful	46,808	99.57

In the case above, the result doesn't include "Server Error". So am looking at adding it and fill in 0 for No. of transactions and percentage

[ITWhisperer · Accepted solution]

| lookup AppResultCode ResultCode as TransactionStatus OUTPUT Status
| append [ | makeresults | eval Status=split("Successful,Authentication Failure,Server Error",",") | mvexpand Status | fillnull value=0 NoOfTransactionsPerStatus PercentTransactions | fields - _time ]
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus sum(PercentTransactions) as PercentTransactions by Status
| table Status NoOfTransactionsPerStatus PercentTransactions

[IsmailK]

This solution worked!! Thank you. Greatly appreciated

[richgalloway]

Perhaps this will help.

index search

| lookup AppResultCode ResultCode as TransactionStatus OUTPUT Status
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus sum(PercentTransactions) as PercentTransactions by Status
| inputlookup append=true AppResultCode
| fillnull value=0 NoOfTransactionsPerStatus PercentTransactions 
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus, sum(PercentTransactions) as PercentTransactions
| table Status NoOfTransactionsPerStatus PercentTransactions