Unpivot columns showing error not accurate data

[mespiritu]

When I'm unpivoting these selected columns one of the values "Unknown - No EID Match" is coming out inaccurate when reviewing the data (YR1 should have 6 "Unknown - No EID Match". The other values seem to match up with the correct numbers when filtering the specific attribute. 

[v-pnaroju-msft]

Hi mespiritu,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi mespiritu,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi mespiritu,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @GeraldGEmerick, for your response.

Hi mespiritu,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Based on my understanding, the error occurs because Power Query automatically set the year columns to the Date data type. When unpivoting, non-date text values such as Unknown - No EID Match cannot be parsed as dates, and therefore appear as errors instead of displaying the original text.

Please follow the steps below, which may help resolve the issue:

1. In Power Query Editor, review the Applied Steps and remove or modify the automatic Changed Type step.
2. Before unpivoting, change the year columns to Text by right clicking the column header and selecting Change Type to Text.
3. Perform Unpivot Columns again as this will preserve Unknown - No EID Match as text.
4. Verify the count for YR1 by filtering for Unknown - No EID Match after unpivoting and check the expected count is 6.

Additionally, please refer to the following links:
Dealing with errors - Power Query | Microsoft Learn
Unpivot columns - Power Query | Microsoft Learn

We hope the information provided helps resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[mespiritu]

It shows 

[GeraldGEmerick]

@mespiritu What is the error when you click on Error?