Forum Discussion
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)
Hi Mcalderon98 ,
This error will occur if you have mixed data types in your column to expand.
Here is a similar thread, hope it can help others who may also have this similar issue easily find the answer.
Solved: Cannot convert value to type table - Microsoft Power BI Community
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- v-jianboli-msft
Community Support
Hi Mcalderon98 ,
This error will occur if you have mixed data types in your column to expand.
Here is a similar thread, hope it can help others who may also have this similar issue easily find the answer.
Solved: Cannot convert value to type table - Microsoft Power BI Community
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.