Forum Discussion

Mcalderon98's avatar
Mcalderon98
Regular Visitor
3 years ago
Solved

We cannot convert the value "not available" to type Table.

Hi, so I am working with files that are hosted on Sharepoint, .json format.  I am working on a logic in which I need to sum a column by the ID (so grouping is the best option) this are my columns:

 

The grouping will be by conversation ID and sum the countParticipant, when trying to do this I get this error:

 

I have checked multiple times and my columns do no have any "not available" value so I don't understand why this is happening. This are the steps that I have done: 

let
Source = SharePoint.Files("https://sharepoint.com/sites/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Pypestream")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Folder Path], "Transcripts")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (9)", each #"Transform File (9)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (9)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (9)", Table.ColumnNames(#"Transform File (9)"(#"Sample File (9)"))),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Table Column1",{"conversation_id", "transcript.participant", "transcript.type"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "countParticipant", each if [transcript.type] = "agent" then 1 else 0),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"conversation_id", type text}, {"transcript.participant", type text}, {"transcript.type", type text}, {"countParticipant", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"conversation_id"}, {{"Count", each List.Sum([countParticipant]), type number}})
in
#"Grouped Rows"

 

 

*note: the steps that start from filtered hidden columns until expanded table column 1 are made by power bi when I combine the files (as mentioned there are multiples files on a sharepoint folder)

 

amitchandak 

1 Reply