Splitting Column into Rows Blank Value Affects Realtionship

DateActionNext ScheduledConsultantFarm NameCrop StageCrop TypeChemistry Recommended

let
Source = Excel.Workbook(File.Contents("C:\Users\sscau\OneDrive\Documents\OneDrive\GROWER FILES\Duncan Macadamia John\Duncan Macadamia.xlsx"), null, true),
#"Pest Monitoring Form_Sheet" = Source{[Item="Pest Monitoring Form",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Pest Monitoring Form_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Submission Date", type datetime}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Adult >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Adult >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Adult >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Adult >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Adult >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Lace Bug Adult >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Aphids >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Aphids >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Aphids >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Aphids >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Aphids >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Aphids >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Broadmite >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Broadmite >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Broadmite >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Broadmite >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Broadmite >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Broadmite >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Feltid Coccid >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Feltid Coccid >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Feltid Coccid >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Feltid Coccid >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Feltid Coccid >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Feltid Coccid >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Fruit Spotting Bug >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Fruit Spotting Bug >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Fruit Spotting Bug >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Fruit Spotting Bug >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Fruit Spotting Bug >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Fruit Spotting Bug >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Green Vege Bug >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Green Vege Bug >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Green Vege Bug >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Green Vege Bug >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Green Vege Bug >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Green Vege Bug >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Thrips >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Thrips >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Thrips >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Thrips >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Thrips >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Thrips >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Mealy Bug >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Mealy Bug >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Mealy Bug >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Mealy Bug >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Mealy Bug >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Mealy Bug >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Macadamia Seed Weevil >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Macadamia Seed Weevil >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Macadamia Seed Weevil >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Macadamia Seed Weevil >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Macadamia Seed Weevil >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Macadamia Seed Weevil >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Bark Beetle >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Bark Beetle >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Bark Beetle >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Bark Beetle >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Bark Beetle >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Bark Beetle >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Flower Catepillar >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Flower Catepillar >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Flower Catepillar >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Flower Catepillar >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Flower Catepillar >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Flower Catepillar >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Looper >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Looper >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Looper >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Looper >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Looper >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Looper >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Rat % Nut Damage >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Rat % Nut Damage >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Rat % Nut Damage >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Rat % Nut Damage >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Rat % Nut Damage >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Rat % Nut Damage >> Zone 6", Int64.Type}, {"Pest Record Number Identified >> Cockatoos >> Zone 1", Int64.Type}, {"Pest Record Number Identified >> Cockatoos >> Zone 2", Int64.Type}, {"Pest Record Number Identified >> Cockatoos >> Zone 3", Int64.Type}, {"Pest Record Number Identified >> Cockatoos >> Zone 4", Int64.Type}, {"Pest Record Number Identified >> Cockatoos >> Zone 5", Int64.Type}, {"Pest Record Number Identified >> Cockatoos >> Zone 6", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Anthracnose >> Zone 1", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Anthracnose >> Zone 2", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Anthracnose >> Zone 3", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Anthracnose >> Zone 4", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Anthracnose >> Zone 5", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Anthracnose >> Zone 6", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Botrytis >> Zone 1", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Botrytis >> Zone 2", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Botrytis >> Zone 3", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Botrytis >> Zone 4", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Botrytis >> Zone 5", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Botrytis >> Zone 6", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Branch Dieback >> Zone 1", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Branch Dieback >> Zone 2", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Branch Dieback >> Zone 3", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Branch Dieback >> Zone 4", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Branch Dieback >> Zone 5", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Branch Dieback >> Zone 6", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Spot >> Zone 1", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Spot >> Zone 2", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Spot >> Zone 3", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Spot >> Zone 4", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Spot >> Zone 5", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Spot >> Zone 6", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Rot >> Zone 1", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Rot >> Zone 2", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Rot >> Zone 3", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Rot >> Zone 4", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Rot >> Zone 5", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Husk Rot >> Zone 6", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Phytophtora >> Zone 1", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Phytophtora >> Zone 2", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Phytophtora >> Zone 3", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Phytophtora >> Zone 4", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Phytophtora >> Zone 5", Int64.Type}, {"Disease Severity % of Flower/Fruits/Nuts Affected >> Phytophtora >> Zone 6", Int64.Type}, {"Date", type datetime}, {"Action", type text}, {"Next Scheduled", type any}, {"Consultant", type text}, {"Farm Name", type text}, {"Crop Stage", type text}, {"Crop Type", type text}, {"Chemistry Recommended", type text}, {"Damage Type", type text}, {"Other Crop Issues", type text}, {"Hours Spent", Int64.Type}, {"Unique ID", Int64.Type}, {"Spray Date Recommended", type date}, {"Notes for Blocks >> 1 >> Information", type text}, {"Notes for Blocks >> 2 >> Information", type text}, {"Notes for Blocks >> 3 >> Information", type text}, {"Notes for Blocks >> 4 >> Information", type text}, {"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Pest Record Number Identified >> Lace bug Nymph >> Zone 1", "Lacebug Nymph Zone 1"}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 2", "Lacebug Nymph Zone 2"}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 3", "Lacebug Nymph Zone 3"}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 4", "Lacebug Nymph Zone 4"}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 5", "Lacebug Nymph Zone 5"}, {"Pest Record Number Identified >> Lace bug Nymph >> Zone 6", "Lacebug Nymph Zone 6"}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 1", "Lacebug Juvenille Zone 1"}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 2", "Lacebug Juvenille Zone 2"}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 3", "Lacebug Juvenille Zone 3"}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 4", "Lacebug Juvenille Zone 4"}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 5", "Lacebug Juvenille Zone 5"}, {"Pest Record Number Identified >> Lace Bug Juvenille >> Zone 6", "Lacebug Juvenille Zone 6"}, {"Pest Record Number Identified >> Lace Bug Adult >> Zone 1", "Lacebug Adult Zone 1"}, {"Pest Record Number Identified >> Lace Bug Adult >> Zone 2", "Lacebug Adult Zone 2"}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"Chemistry Recommended", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Chemistry Recommended"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Chemistry Recommended", type text}})
in
#"Changed Type1"