data format error but the value returning the error doesn't exist?

[brendo]

Hi -- I can't share the data I am using because it is not public information. However I will do my best to explain the issue. 

I have a dataset that I am performing several operations on. All steps are successful until I pivot a column. Then I get a data format error that the value "mr2021" could not be turned into a date. The issue is, this value doesn't appear to exist in my dataset. To try to find the value, I queried my dataset until the step where I pivot the data and the query fails, and I exported this data to R. The value "mr2021" doesn't exist in my data. Also, the key-value columns that I'm pivoting only contain text values. I still get this error that the value "mr2021" cannot be turned into a date. I've also tried converting all my columns to text before pivoting but for some reason during the pivot step power query still finds this "mr2021" value and tries to convert it to a date...

In my dataset, I only have two columns with date values. And in previous steps I replaced all errors in those columns with null values. So it doesn't seem like this error is coming from my date columns.

I'm truly stumped as to what to do. Does anyone have experience dealing with a similar situation?

[samdthompson]

Hello, bit hard to tell without seeing but the two steps id take would be:

1. Make sure that on pivot the Advanced options >>> Aggregate Value Function is set to Don't aggregate

2. Create a step immediately prior that generates an index column which you delete in the subsequent step. An alternate here is to Table.Buffer() but i prefer the UI index method. PQ can be a bit odd with where it takes data from and the inserting an index forces it to readt from the previous step.

Let me know how you go.

Cheers,