SharePoint List and null values

Hi fxrofthngs 

You're on the right track - you can add a step that uses Table.TransformColumns to convert non-list values to nulls, before expanding the list column. Null values won't cause an error and will be left as null.

The final code would be something like this, with a "Fix Non List Values" step added:

let
Source = SharePoint.Tables("https://***.sharepoint.com/sites/***", [Implementation="2.0", ViewMode="All"]),
#"****" = Source{[Id="****"]}[Items],
#"Extracted Values" = Table.TransformColumns(#"****", {"Department", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Expanded Primary Responsible" = Table.ExpandListColumn(#"Extracted Values", "Primary Responsible"),
#"Expanded Primary Responsible1" = Table.ExpandRecordColumn(#"Expanded Primary Responsible", "Primary Responsible", {"title", "email"}, {"Primary Responsible.title", "Primary Responsible.email"}),
#"Fix Non List Values" = Table.TransformColumns( #"Expanded Primary Responsible1", {{"Secondary Responsible", each if Value.Is(_,type list) then _ else null}} ),
#"Expanded Secondary Responsible" = Table.ExpandListColumn(#"Fix Non List Values", "Secondary Responsible"),
#"Expanded Secondary Responsible1" = Table.ExpandRecordColumn(#"Expanded Secondary Responsible", "Secondary Responsible", {"title", "email"}, {"Secondary Responsible.title", "Secondary Responsible.email"})
in
#"Expanded Secondary Responsible1"

 Does this fix it at your end?

Regards,

Owen