Splitting Column into Rows Blank Value Affects Realtionship

[sustainhort]

 am hoping someone can help, i have split a Column into Rows and the values (text) are showing up as blanks in a new row, the original first row has the value showing. It is also creating an additional row with no value, basically an extra row. I need and have a relations hip formed although as the first row has only the value showing it is showing only the products related to this. I have set the data to show items with no data.

[KNP]

Can you please provide some sample data and Power Query code?

It is too difficult to solve with the detail provided.

[sustainhort]

does this assist?

[KNP]

Not really. Something that I don't have to re-type.

Just copy/paste data, not screenshot, and also copy/paste PQ code from the advanced editor (obfuscate any sensitive info). 

[sustainhort]

DateActionNext ScheduledConsultantFarm NameCrop StageCrop TypeChemistry Recommended

Jul 18, 2022 01:00 PM	Treatment Required	Georgina Gill Mark Duncan Marcus Lullham	Byrne - Dalwood	Pre Flower Open Flower	Macadamia	Copper oxychloride, Phosphoric Acid, Carbendazim,
Jul 18, 2022 03:00 PM	Treatment Required	Aug 1, 2022	John Hay Andy Cameron	Byrne - Richmond Hill	Pre Flower Open Flower	Macadamia	Methoxyfenozide, Carbendazim, Copper Hydroxide,

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"

[KNP]

Thanks for that, it's making more sense to me now.

If you right click on the 'Chemistry Recommended' column and choose 'Fill >> Down' does this get you closer to what you want?

You'll probably need to add one more step to filter the blank rows you were seeing.

[sustainhort]

I have filled down and this has not changed the table and relationship data

[KNP]

Did filling down fill in the blanks or not?

[sustainhort]

no

[KNP]

I don't think I'm going to be able to troubleshoot like this. If you can share the Excel file and PBIX file I may be able to work it out. 

Possibly, before the fill down step, click in one of the empty cells, right click and choose 'Replace Values' , enter null in the second box, then do the fill down after that step.

[sustainhort]

If i go into each cell and replace the value if(name) with the actual word to remove the blank the relationship is fine ad the table visual shows everything. So the issue is that the splitting is moving it to blank cells although if clicking on the blank cell the data is there

[KNP · Accepted solution]

You've got carriage return and line feed characters in your data. 

That will be causing you issues in your split.

Go back to the step BEFORE the split, right click on the column and choose 'Transform' and 'Clean'.

This should remove any unnecessary white space.

Hopefully this will prevent what appears to be blank rows.

[sustainhort]

Thanks for your help there that worked a treat

[KNP]

Awesome, glad we got there in the end.

If you could 'Accept as Solution' on the post that solved this for you, that'd be appreciated.