'Table' and 'List' showing in Power Query columns that should be peoples names or a business unit

[Anonymous]

Hi All,

Still a PBI noob so I hope someone can assist 😀

I have successfully merged three SharePoint List sources into one table in my PBI Desktop.

All the data is fine except two columns from two of the sources. One should display a name, the other a Business Unit.

Issue 1

Owner column displays 'Table' instead of a persons name for one of the data sources.

I have checked the consistency of the SharePoint List settings for this column in all three sources and they are identical in that only one name is entered in each record as the owner and the associated SharePoint List settings are the same.

Issue 2

Business Unit column displays 'List' instead of a Business Unit name for a different data source.

Again, I have checked the consistency of the SharePoint List settings for this column in all three sources and they are identical in that only one Business Unit is entered in each record as the Business Unit and the associated SharePoint List settings are the same.

I recall having this issue a while back but cannot for the life of me remember how I resolved it. 

I am sure it was something to do with the Power Query formatting or the column itself in extracting the Values.

Anyway, hope someone can guide me right.

Cheers

[Peter_Beck · Accepted solution]

Hi -

Not sure why your data ended up this way, but these are called "structured columns" i.e. each entry in the column contains a "structure", in this case a table or a list.

Here is a good article about how to "expand" these columns:

https://support.microsoft.com/en-us/office/work-with-a-list-record-or-table-structured-column-power-...

Basically, just click on the little icon at the top of the column and pick the values you want to extract. Because your table only contains one row it should not add additional rows to your overall table.

Hope this helps,

Peter.

[gg082295]

hi, i am having this same issue! except when i "Expand" it creates multiple rows to my table, any suggestions on how to fix?

[Anonymous]

Hi @gg082295 - In the column header to the righ tof the column label I assume you have a double opposing arrow icon? Right click on that and select Expand with Values (if I recall correctly). Select the value you want to use, eg name, and apply. Let me know how that goes.

[Anonymous]

@Peter_Beck Many thanks for the information. 😀

That is exactly what I recall doing last time. Thanks. I think where I complicated things for myself was appending the three source tables without properly transforming the data in each table first.

In this case, to address the 'structured column' issue.

As a result, in my appended table, I have an 'Owner' column that has a mix of names and 'Table' and the option to expand is not available.

All I can think, through this learning, is to restart, import the sources one at a time, transform them properly with this refreshed information, and THEN append.

UPDATE:

That did it. I started fresh and followed a more logical approach.

1. Imported the 3 source lists

2. Transformed each one individually to ensure all columns were matched including expanding any structured columns 😂

3. Appended all 3 into a new table

4. Done!!

Thanks again

Cheers