Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Please can someone help with the issue I'm facing, detailed below:
I have taken the original data structure of:
------ 01/01/2017 Feb 2017 22/03/17
Team Min Max
and cleaned it a little to now show:
------ 01/01/2017 01/02/2017 01/03/2017
Team Min Zero Max
I then pivot the date columns (however they are actually a 'text' datatype) so the data structure is now:
Team 01/01/2017 Min
Team 01/02/2017 Zero
Team 01/03/2017 Max
All good so far.
When I select 'Apply', from within Power Query I get the following message Just after it loading to the data model:
"OLE DB or ODBC error:[DataFormat.Error] We couldn't parse the input provided as a Date value.."
This error message only appears after the pivoting step and not before. All columns at the 'text' datatype.
Please can someone tell me what the problem is?
Thanks.
Solved! Go to Solution.
The issue has been resolved.
The solution: a simple delete of the table and then reconnect to the source file using a fresh 'Get Data' option.
Doing the pivoting now works without issue.
Strange how I was receiving the error in the first place only for a fresh connection to resolve it. The content of the datasource was unchanged.
Thanks to all who contributed.
@Anonymous
Hi,
It seems that the “Min” is in your date column. You can reference the picture below. Does your model connect to the database? When “Apply”, PBI will fetch data from database where may be contain incorrect data.
Best Regards!
Dale
Thanks Dale.
The error you have shown is the exact same error message I receive.
The puzzling thing is, the data is being pulled from a .xlsx file - in the following format:
Team May 16 June 16 Jul 16 Aug 16 Sep 16
Operations Min Max Min
Production Max Min Min
I then manuipulate the above to be:
Team Attribute Value
Operations 01/05/2016 Min
Operations 01/06/2016 Zero
Operations 01/07/2016 Max
Operations 01/08/2016 Min
Operations 01/09/2016 Zero
Production 01/05/2016 Max
Production 01/06/2016 Min
Production 01/07/2016 Zero
Production 01/08/2016 Zero
Production 01/09/2016 Min
I first manually amended the date names (i.e. 'May 16' to be '01/05/2016' for all the date names).
I then replace all null values with 'Zero'.
I then pivot the Attribute date names so to list vertically (thus becoming the Attribute column, with the automatic addition of the Value column too).
I then click 'Apply' and that's when I get the error. I get the error even when I try the following:
Attribute is a text column, Value is a text column.
Attribute is a date column, Value is a text column.
Removing error rows on both the Attribute and Value columns.
Replacing errors with '31/12/9999' for the Attribute column, and 'ZZZZ' for the Value column.
None of the above resolve it.
The puzzling thing is, the error 'Min' value can only be present after my pivoting.
I'm lost. Are there any tricks I can do to pinpoint the error? and then to remove/delete it?
Thanks.
The issue has been resolved.
The solution: a simple delete of the table and then reconnect to the source file using a fresh 'Get Data' option.
Doing the pivoting now works without issue.
Strange how I was receiving the error in the first place only for a fresh connection to resolve it. The content of the datasource was unchanged.
Thanks to all who contributed.
@Anonymous
Hi,
My pleasure. Could you please post some snapshot? Snapshot 1: the data in excel file. Snapshot 2: the status of after unpivoting. If you can post the PBIX, that would be great. You can find the error as picture showed.
Best Regards!
Dale
@Anonymous sounds like you have invalid dates?
in power query it only profiles the first 200 rows when you apply and load you are applying to all your data, you might have to do some profiling of your data
Proud to be a Super User!
Thanks for your time Vanessa.
For the columns with dates for the heading (i.e. 01/01/2017) I did the following steps:
1) Manually enter the columns to be dd/mm/yyyy format. I did this for all date columns (01/01/2017 through to 01/12/2020). So I know the column headings are correct.
2) I then set columns from 01/01/2017 through to 01/12/2020 as 'text' datatype.
When pivoting them I have confidence the Attribute column (i.e. containing the dates (i.e. 01/01/2017)) have correct dates and the Value column is set to 'text' too. So the two columns that have been affected by the Pivoting are correct.
So if I have manually cleaned the data and can see the column's values are correct then what is happening to warrant this error message?
@Anonymous its really hard to know off the top of my head without seeing the data
is there any chance you can share your pbix?
Proud to be a Super User!
Not really due to the sensitve data.
I did try and replicate the error through creating a a spreadsheet of the data matching that like the original, and reproducing the same steps in a new .PBIX file (i.e. setting the columns to 'text', manually renaming the columns to 01/01/2017 etc.., and then pivoting them. All worked fine. So its not the steps but the data.
In the error version of the .PBIX file I removed the 'Applied Steps' and then reapplied them but in a different order. When attempting to 'Apply' the changes within Power BI I get the same error. The only difference I can see as my 208th, and last, row that each column entry for that row has Error in yellow appearing there.
When clicking on the Error in each of the columns I get the error "DataFormat.Error: We couldn't parse the input provided as a Date value. Details: Min".
So clearly there is an error present. Something to do with 'Min'.
I really do not know what's going on?
@Anonymous
can you put a screen shot, what i interpret that as is having a text value of 'min' in your date column which will spit out an error, do a replace on it, with a dummy date or with nothing and see what happens
Proud to be a Super User!
This is a strange one.
The source data is not tidy. What I am attempting to do now is get the end-user beyond their sticking point but at some point they will need to clean their data which may break what I'm producing for them. I will go back to the end-user and request they clean up their data. Once they do this, if the error is gone then the lesson learned is cleaner data, if it remains then I can removed alot of the applied steps, used to clean up their data, and play with the vanila source to seek a resolution to this error.
Thanks for your advice on this.
@Anonymous Is always best to push cleaning back to the source as much as possible but as you know power query is a great data cleaner too! Good luck
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |