Forum Discussion
PowerQuery converting text to date automatically
Hi,
I'm using PowerQuery to read in a .csv file. The key field for the file is a text field, and most records have a random alphanumeric string for this field, but I have a handful of records where this text field in the original data is "january-2022" or "april-2023", for example. This is causing an error with the PowerQuery model, as it says this field is only meant to accept text, and cannot accept Dates. How can I force PowerQuery to accept "january-2022" or even "january2022" as a string/text, rather than try to parse it into a date unprompted?
Thanks!
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.
8 Replies
Hi,
It sounds like it is the Change Type step that is automatically added that is causing you the problem.
You can either delete this step or change the problem column to 'type text'.
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.
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}})
Can I get the entire code from the advanced editor rather than just the one step?
Just change anything that is sensitive before posting.
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.
After loading data into the Power Query, a step namely change type will be added, Remove that step.
