Forum Discussion
PowerQuery converting text to date automatically
I'm not sure why, but I managed to get the data to ingest by modifying a filter step. Basically, I had a step that filtered on gift_date (the only Date field in the database), but changing that filter to be dependent on gift_fiscal_year (an Int) instead, seems to have made the model happy. Thanks for you help! I'm going to close this post now.
Hi, thank you for your response. I verifed that this field is set to "type text" in the PowerQuery model. It seems like that might be the root of the error; that it's looking for text but instead sees data it interprets as a date, and therefore won't allow it to be ingested as Text. But I would like the "dates" to be ingested as text... and I'm not sure how to tell that to PowerQuery / Excel. I'm not sure how to tell Excel...you THINK this is a date, but it's not actually. It's text.
- KNP
Super User
That doesn't sound right to me. If it is set to text it shouldn't be returning this error.
Do you have any other Change Type steps in your query?
If you can post the code from the advanced editor (hide any sensitive info) I'll be able to help you better.
This is the code from the "Promoted Headers" / "Changed Column Type" step. "source_code" is the problem field.
Table.TransformColumnTypes(#"Promoted headers", {{"client", type text}, {"source_code", type text}, {"gift_fiscal_year", Int64.Type}, {"gift_calendar_year", Int64.Type}, {"gift_month", Int64.Type}, {"gift_date", type date}, {"program", type text}, {"campaign", type text}, {"camp_id", Int64.Type}, {"package_id", type text}, {"seg_id", type text}, {"package", type text}, {"campaign_fiscal_year", Int64.Type}, {"mailstats_flag", type logical}, {"channel", type text}, {"sub_channel", type text}, {"fund_description", type text}, {"gifts", Int64.Type}, {"revenue", type number}})
