cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EricDeferm Frequent Visitor
Frequent Visitor

Column of data type 'Table' doesn't provide any columns when expanding

I'm using Power BI Desktop version 2.54xxx

I've used Online Sharepoint List as source type to access a document library within a SP site collection.

After selecting the source, the query builder shows a list of all the components within the Library (standard fields, custom fields, records and tables).

Some of the columns of data type 'Table' do show a list of columns when you expand them (idem dito for records), but some columns of data type 'Table' don't. F.i. the 'Activities' & 'Versions' column don't popup a list of columns when expanding.

I always get the message 'No columns were found'

NoColumnsWereFound.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Column of data type 'Table' doesn't provide any columns when expanding

So it looks like your nested tables are all empty.

 

It's up to you to decide what to you want to happen in such case.

 

Suppose you always want to expand columns Column1 and Column2 from the nested tables, wether or not these columns are available, then you can transform the column type of the column with nested tables to a type table with Column1 and Column2, which will allow you to expand the table.

 

let
    Source = #table(type table[ID = number,Activities = table],{{1, Table1}}),
    TransformColumnType = Table.TransformColumns(Source, {{"Activities", each {_}{0}, type table[Column1 = any, Column2 = any]}}),
    #"Expanded Activities" = Table.ExpandTableColumn(TransformColumnType, "Activities", {"Column1", "Column2"})
in
    #"Expanded Activities"

 

Another alternative is that you still want to rely on the actual columns that are available (or not) and you may need to design something else, but you didn't provide the information required to design a suitable solution.

 

Your question in red looks quite specific for OData sources and I am not familiair with that.

Specializing in Power Query Formula Language (M)
4 REPLIES 4
Super User
Super User

Re: Column of data type 'Table' doesn't provide any columns when expanding

Probably all nested tables are empty.

If not, then you can add a step before expanding, in order to transform the column type of the "Activities" column to the table type that includes all columns of the nested tables.

 

Note: replace "NameOfYourPreviousStep" (2x) with the name of your previous step in the Query Editor.

 

TransformColumnType = Table.TransformColumns(NameOfYourPreviousStep, {{"Activities", each {_}{0}, Value.Type(Table.Combine(NameOfYourPreviousStep[Activities]))}})
Specializing in Power Query Formula Language (M)
EricDeferm Frequent Visitor
Frequent Visitor

Re: Column of data type 'Table' doesn't provide any columns when expanding

Hi @MarcelBeug thank you for the prompt reply. I try to apply your suggestion but without success. I still receive no columns found after expanding the 'Activities' column. Any idea what I'm doing wrong. Below I've copied the syntax as you suggested. Is it correct? Tx, your help is truly appreciated.

 

let
    Source = SharePoint.Tables("https://xyz.sharepoint.com/sites/<SP site name>", [ApiVersion = 15]),
    #"2524f6db-0289-4fdf-8e92-5c7338ef0518" = Source{[Id="2524f6db-0289-4fdf-8e92-5c7338ef0518"]}[Items],
    #"TransformColumnType1" = Table.TransformColumns(#"2524f6db-0289-4fdf-8e92-5c7338ef0518", {{"Activities", each {_}{0}, Value.Type(Table.Combine(#"2524f6db-0289-4fdf-8e92-5c7338ef0518"[Activities]))}}),
    #"Renamed Columns" = Table.RenameColumns(#"2524f6db-0289-4fdf-8e92-5c7338ef0518",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

 

I also tried it on another table 'Versions', but in that case we receive an error:

DataSource.Error: We couldn't parse OData response result.  Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value.
Details:
    DataSourceKind=SharePoint
    DataSourcePath=https://xyz.sharepoint.com/sites/<Sharepoint site name>/_api/Web/Lists(guid&#39;2524f6db-0289-4fdf-8e92-5c7338ef0518&#39Smiley Wink/Items(2)/Versions

Super User
Super User

Re: Column of data type 'Table' doesn't provide any columns when expanding

So it looks like your nested tables are all empty.

 

It's up to you to decide what to you want to happen in such case.

 

Suppose you always want to expand columns Column1 and Column2 from the nested tables, wether or not these columns are available, then you can transform the column type of the column with nested tables to a type table with Column1 and Column2, which will allow you to expand the table.

 

let
    Source = #table(type table[ID = number,Activities = table],{{1, Table1}}),
    TransformColumnType = Table.TransformColumns(Source, {{"Activities", each {_}{0}, type table[Column1 = any, Column2 = any]}}),
    #"Expanded Activities" = Table.ExpandTableColumn(TransformColumnType, "Activities", {"Column1", "Column2"})
in
    #"Expanded Activities"

 

Another alternative is that you still want to rely on the actual columns that are available (or not) and you may need to design something else, but you didn't provide the information required to design a suitable solution.

 

Your question in red looks quite specific for OData sources and I am not familiair with that.

Specializing in Power Query Formula Language (M)
Highlighted
vengadeshpalani Regular Visitor
Regular Visitor

Re: Column of data type 'Table' doesn't provide any columns when expanding

Hi @EricDeferm

 

How did you fix this Issue? (Highlighted in red)