Append Queries expand column not showing for field values as text

[Brendan_McLaren]

i have combined several lists with the append queries and now the expand column function is not giving me the option, i was about a week ago now it has disappeared,

if i go to the original queries they show up as being able to expand

any suggestions?

[MFelix]

Hi @Brendan_McLaren ,

Believe that some concepts are missing in your questions when you append data you combine tables one after the other so a single table grows on number of lines, when you merge tables then you have the possibilitie of expanding columns.

Or are you appending tables that have expandable columns and you are only expanding them after the append?

Regards,

MFelix

[Brendan_McLaren]

i took several lists that all have the exact same columns and have a column called fieldastextvalue that is expandable and appended them, and expanded the column

for a few weeks this worked no problems and now the exapnded columns show error and i cannot redo and append the lists again and have that field that expands. so if i take the exact same lists and try it again it doesnt work, wondering if there was an update that changed this or if there is a work around to expanding those columns

the queries that i am appending are workflow task lists form 30 different sites and lists

[MFelix]

Hi @Brendan_McLaren ,

Have you tried it with onyl a single  list and see if the expanded column is there and if you can expand it?

What is the error code that PBI is giving you?

Regards,

MFelix

[Brendan_McLaren]

i am not sure what the error is, but it looks like if i have an empty task list it causes an error through out the entire thing, this didnt happen a few weeks ago so microsoft might have updated something,

do you know how i would get that error code?

[MFelix]

Hi @Brendan_McLaren ,

Have you tried to make the update of the data model to your PBIX file?

When you update a datasource on PBI desktop if there are errors on conversion of data it will return an error table.

Regards,

MFelix

[Brendan_McLaren]

i have tried several things,

i think the error might be from a few of the lists being empty right now, if  i only use ones that have information in them then it works

but it was working a few weeks ago no problem, maybe microsoft updated something?

[MFelix]

Hi @Brendan_McLaren ,

Can you go step by step on your query and check where it breaks the code? If it's before the expansion of the tables?

Regards,

MFelix

[Brendan_McLaren]

there isn’t any code to go over, the columns can be expanded if I only append lists that contain information not just a blank list with column headers.

basically, the lists with information in them can be expanded before and after appending them, but as soon as I append a list into the mix that doesn’t contain any information all of the other data for any expanded columns just says error,

if I remove the lists that do not contain any information then the columns become expandable again.

when I originally published this report to the online the lists that currently do not contain information did not cause any errors, but now if I try and do it then there is an error, so I am wondering if there was an update in august some time that is causing this

[MFelix]

Hi @Brendan_McLaren ,

Not exactly sure of what is happening, however believe that somenone like @ImkeF  maybe can help you because she is a M language expert.

@ImkeF , can you help on this question do you know any way to check if the sharepoint list is empty and ignore it so that when the append is made it's possible to make the expansion?

Regards,

MFelix

[ImkeF · Accepted solution]

Hi @Brendan_McLaren ,

could this be the solution to your problem?: https://www.thebiccountant.com/2017/07/25/how-to-expand-a-column-that-cannot-be-expanded-in-power-bi...

[Brendan_McLaren]

that seems to have worked! thank you so much!!!