Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EricDeferm
Regular 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

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)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

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)

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&#39;)/Items(2)/Versions

Anonymous
Not applicable

Hi @EricDeferm

 

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

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.