Forum Discussion
"we cannot convert the value null to type Number" after merging queries on date column
Hi Power Query people
I am rushed off my feet trying to get my head around this error in Power Query which I am getting after clicking on close and load:
I saw a couple of similar topics, but it did not help me figure out my case (perhaps I've just got too little experience with PQ to translate other solutions to my situation.....sorry!)
So this is a look up table I am merging with the fact table, to bring over the text column "ISO year.week":
This is the Merge step in the main fact query (until here the query was loading without any errors):
This is the result -> for every date in column "_Est.Stuffing date" I get a corresponding ISO year.week from the look_up table:
The type of the new column is still text, and I also replaced errors with "null" in case there were any (in both these columns).
But when I close and load I get the a.m. error message with "we cannot convert the value null to type Number".
Any idea how to resolve it please? 😥🙏
oh this is incredible, I just discovered a (simple) solution to that nasty error!
So the data type of the columns I was doing a merge on was correct and was not the issue (Sailing and Stuffing dates as well as Date column in the lookup table are all type Date). Errors were replaced with null.
The issue was caused apparently by the text field I was bringing in. Once I replaced null values in that text column the data finally loaded successfully!
thank you very much in any case, you are super helpful, really appreciate that 🙏
7 Replies
oh this is incredible, I just discovered a (simple) solution to that nasty error!
So the data type of the columns I was doing a merge on was correct and was not the issue (Sailing and Stuffing dates as well as Date column in the lookup table are all type Date). Errors were replaced with null.
The issue was caused apparently by the text field I was bringing in. Once I replaced null values in that text column the data finally loaded successfully!
thank you very much in any case, you are super helpful, really appreciate that 🙏
- BA_Pete
Super User
Hi kadabrada ,
If you think the merge is causing you issues, then just get rid of it.
Send both your fact table and your lookup_calendar table to the data model and relate lookup_calendar[Date] to factTable[_Est.Stuffing date].
You should probably do this anyway as best practice. It will speed up your refresh time and simplify your PQ query to assist in debugging issues like the one you have described.
Pete
- BA_Pete
Hi kadabrada ,
Just relate it to both. Relate it to your most-frequently used date first (so it's active) and your less-frequently used date second (will be inactive).
Use the USERELATIONSHIP function in your measures to specify which date you want to use in any calculations.
More info from MS here:
Pete
Hi Pete
Thanks for the idea of the USERELATIONSHIP function. It sounded indeed like the solution, but 1) something is not working right when I create a new measure with this function - perhaps I am doing something wrong; and 2) I cannot use data from the look up table in the way I need to. I will try to explain below.
1) So, I have a fact table with transactions, with est.Stuffing and est.Sailing dates per transaction.
I have a separate look-up table with a long list of dates and corresponding text field ISO year.week - this is the key field which I need to have for both dates in the transaction table individually.
I created connections between est.Stuffing date and Date column in the lookup table (=primary connection), and second inactive connection between est.Sailing date and Date column in the lookup table.
Then I created a new measure:
But when in the pivot table I pull in the ISO year.week field I do not get any values for the created measure:
The other two columns here are original columns from the transactional table.
2) I need to build a key using two fields: one from the transactional table and another one is the ISO year.week from the look up table. Measure with USERELATIONSHIP will not help me in this case. I need to have the ISO year.week as an individual column in the transactional table for that (I do not know if there is any other way to achieve this). But merging the queries is still giving me an error...
😪
- v-angzheng-msft
Community Support
Hi, kadabrada
May I ask if your problem has been solved? Is the above post helpful to you?
If it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.
If not, could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented? And It would be great if there is a sample file without any sesentive information here.It makes it easier to give you a solution.
- Sample (dummy dataset) data as text, use the table tool in the editing bar
- Expected output from sample data
- Explanation in words of how to get from 1. to 2.
Best Regards,
Community Support Team _ Zeon ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
