The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Problem: Valid dates from the source show up as null in Power Query at the very beginning of the query.
Situation: I'm working on a project that pulls in data from an Excel (.xlsx) source file via Power Query in Excel. There are a few columns in the source file with dates that use a custom date format. The 2nd step in my query, after my source step, expands the table. At this point, the dates are already showing as null. I'm having the same problem with a 2nd file that uses a custom cell format in the .xlsx source file.
Solutions Tried:
What Works: If I open the source file and reformat the column with the dates as general, the dates no longer show as null from the beginning.
What I Need: Ideally, I'd like not to have to manipulate the file each time and be able to pull the date values in automatically.
Please help! Any help is appreciated! 😀
For some reason, I can't tag the Biccountant, @lmkeF. It would be great if you could help lmkeF!
Solved! Go to Solution.
@nerd_in_NE - there are issues with XLS files. One example is here but I've seen two others in recent months. Power Query cannot change the files - it is query only - and cannot reliably get info if the formatting is off in the file in some way. You have two choices:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUnfortunately no @nerd_in_NE - you are at the mercy of the tool exporting the file. I've never seen issues with CSV/Text files, but XLS is a problem. The fix is, unfortunately, open in Excel, and force it to save as XLSX. Excel will fix any issues.
You could automate that via a Power Automate flow though if you are good with that tool.
Please mark one of these as the solution if it helps, and give thumbs up to anyone that has helped. Thanks!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
Thanks for the quick reply!
I believe that the client's exports from 2 different systems are outputted as .xls files. The client then saves those files as .xlsx files. The client is using Excel for Mac, and Power Query currently only works with .xlsx files on Excel for Mac (unless I'm mistaken).
@nerd_in_NE - there are issues with XLS files. One example is here but I've seen two others in recent months. Power Query cannot change the files - it is query only - and cannot reliably get info if the formatting is off in the file in some way. You have two choices:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @nerd_in_NE
if you have a flat file (meaning only one sheet with one table in it) store the file as .csv-file then you are on the save side. Otherwise storing as xlsx-file should also always work out. (At least I never encountered something different)
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @edhans
just as @edhans is saying, the xlsx-file is probably stored in a bad way. Most of this issues are solved by opening the file one time in Excel and make a save/save as.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@nerd_in_NE are you 100% sure these are XLSX files, or could they be XLS (Excel 2003/2007 format) files? I have seen more and more issues where formatting in XLS files cause issues in Power Query.
Also, could they be truly 2003/2007 XLS files that some external system is exporting as XLSX? There are a ton of ERP system that do a horrible job confirming to MS Excel format standards, and the file opens ok in Excel, but outside systems, like Power Query, can choke on it.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingJoin the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
24 | |
23 | |
21 | |
16 |