You can call the Patch function multiple times, one for each dropdown that has a corresponding value greater than 0. Assuming that the AmountX values are entered in text input controls (called AmountTextInput[1-5]), the SampleDate is entered in a Date Picker control (named SampleDatePicker), and the [Company/Site/Consultant] names are entered in text input controls (named [Company/Site/Consultant]NameTextInput), then you can have this formula (where 'ExcelTableName' is the name of your Excel table):
If(Value(AmountTextInput1.Text) > 0,
Patch(
'data source name',
Defaults('data source name'),
{
SampleData: Text(SampleDatePicker.SelectedDate, "dd-mm-yyyy"),
CompanyName: CompanyNameInputText.Text,
SiteName: SiteNameInputText.Text,
ConsultantName: ConsultantNameInputText.Text,
ContaminantSampled: Dropdown1.Selected.Value,
SampleAmount: AmountTextInput1.Text
}));
If(Value(AmountTextInput2.Text) > 0,
Patch(
'data source name',
Defaults('data source name'),
{
SampleData: Text(SampleDatePicker.SelectedDate, "dd-mm-yyyy"),
CompanyName: CompanyNameInputText.Text,
SiteName: SiteNameInputText.Text,
ConsultantName: ConsultantNameInputText.Text,
ContaminantSampled: Dropdown2.Selected.Value,
SampleAmount: AmountTextInput2.Text
}));
...
If(Value(AmountTextInput5.Text) > 0,
Patch(
'data source name',
Defaults('data source name'),
{
SampleData: Text(SampleDatePicker.SelectedDate, "dd-mm-yyyy"),
CompanyName: CompanyNameInputText.Text,
SiteName: SiteNameInputText.Text,
ConsultantName: ConsultantNameInputText.Text,
ContaminantSampled: Dropdown5.Selected.Value,
SampleAmount: AmountTextInput5.Text
}))
