SharePoint List and null values

[fxrofthngs]

I am new to Power Query, so hopefully this is an easy answer to the experts out there.

I am trying to creating a report using a SharePoint list as the data source. In this list are two columns that are "Person or Group" type. I am able to connect to the list, and figured out how to expand the records so I see the actual value and not the 'List' type in the column. This all works until there is no value in one of these columns, which causes the query to error out.

OLE DB or ODBC error: [Expression.Error] We cannot convert the value "" to type Table..

What I want is for the query to essentially skip this column and keep proccessing the list. I want the rest of the affected record to remain. It is possible these columns will not always have data in them. From what I have found, it seems like I need some kind of If-Then statement to determine if it is a blank value, and if not expand the the list/record to produce the actual values. I am unsure of how or where to put this into the query. Snapshot of the data (prior to transform) and query below. Any suggestions?

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"}),
#"Expanded Secondary Responsible" = Table.ExpandListColumn(#"Expanded Primary Responsible1", "Secondary Responsible"),
#"Expanded Secondary Responsible1" = Table.ExpandRecordColumn(#"Expanded Secondary Responsible", "Secondary Responsible", {"title", "email"}, {"Secondary Responsible.title", "Secondary Responsible.email"})
in
#"Expanded Secondary Responsible1"

I am currently getting the error at the "Expand Secondary Responsible" step, because that is where the blank value is currently. what ever solution I come up with will need to be implemented for both Primary and Secondary columns.

Appreciate any pointers or help here.

[OwenAuger · Accepted solution]

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

[fxrofthngs]

That is perfect, stops the error and let's the rest of the list load. Thanks Owen!