Log column that expands a strange problem :(

[pollyannaog]

Hey, everybody, how are you?

I'm facing a strange problem with query transformation in Power BI Desktop. I have a dataset where "column1" is a list of data (see image below):

When I expand "column1" it looks like it's a record list colum, with some null rows (see image below):

Therefore, I again apply an expansion, now in this column of records. So, it looks like everything worked well (see image below):

But... When I apply all transformations, Power BI displays this error:

It seems that few row records that were expanded did not match the expected structure. I've tried many transformations without success and searched the web, but I can't find a solution for this. Can someone help me?

Thank you!!!

[mahoneypat]

I am not sure what the M code of the expansion step looks like without having similar data, but if it includes an each expression (per row transformation), you could wrap the existing each function in try <existing M> otherwise "Had An Error".  That way you could refresh and see which rows are causing the error so you can investigate further.

Regards,

Pat

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[v-shex-msft]

HI @pollyannaog,

Did you dataset table structure dynamic changes? AFAIK, power query will cache the previous processing table structure into query steps.

If some records data structure or stored data type changes so that they do not match with the cached data structure, power query will return an error message about the wrong type/structure transform.
Regards,

Xiaoxin Sheng

[pollyannaog]

It does make sense, I hasn't think about it...thats why the error only happens when I apply the transformations.

[AlB · Accepted solution]

Hi @pollyannaog 

Can you share a mock pbix that reproduces the problem?

Looking at the error message it looks like PQ encounters a record where it expects a table. You could check if the item is a record with the following (it will yield true if it is a record):

Value.Is([YourColumnName], type record)

and then, if it is a record either ignore it in the expansion or perhaps convert it to table, depending on what you prefer.

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[pollyannaog]

Thank you @AlB , with this trick I was able to create a custom column with the type of each row and then find out the one with different type!!