Forum Discussion
Date data showing as "null" despite source data having values
Hi Guys
I have a strange one and im a bit of a power query newbie.
I am wanting to import 6 monthly excel files from a file using the "from file" function. They all have the same format and structure. When I import and select "combine & transport data" only the dates for the sample workbook (Apr) seem to appear, no dates will appear for the Jan, Feb, Mar, May or June (unless i select them for the sample data). They will just appear as "Null".
After researching online, im told becaue the dates are US formatted (im in Australia) that this could be causing issues. To test this I used the import "from excel work book" for Feb and April (as two separate queries). For the Feb and April queries, i select "Use first row as headers, and then change column type (for the date column) to using locale --> Data type = "Date" --> Locale = "English (United States)". I then use "append queries" to join the two together. This seems to solve the problem of the dates not showing:
My question is, is there a way i can get the dates to show for all months using the "from file" function. Essentially I dont want to have to make individual queries for each new month, I just want pull all the data out of the file. I also don't want to have to change the source data. Any tips or tricks for be really helpful!
Thanks
Hi Anonymous ,
In your first screenshot, the column that has the null dates in it is called [APRIL PRODUCTS] and shows April dates. I doubt that the appropriate column in the February file is called [APRIL PRODUCTS] so Power BI can't append these two columns together. I suspect that further along in your table there will be a column called [FEBRUARY PRODUCTS] that only contains Feb dates and no Apr dates.
In your second screenshot, contrary to the above, it appears as though the column in both tables was named [Delivery Date], so Power BI would be able to append these fine.
When you import, I would recommend the following steps:
-1- Use the Folder Connector on the folder where all your monthly reports get saved and select Combine & Transform.
-2- Filter the top-level folder table in your final append query, probably on [Date created], to get the latest six dates, or however you need to filter to dynamically get the appropriate files to be appended.
-3- In your Transform Sample File query that gets automatically generated, ensure that you change any file-specific column names to generic column names. You may need to be a bit clever with this as you don't want to hardcode the specific column name that you're changing from (as this won't work on other files with other column names) but if you search for "Dynamically change column names", you'll find a number of simple ways to do this.
Once you've created generic column names in your sample file, Power BI will apply these transformations to all of the files you've filtered in step -2-, and will append them as required.
Pete
No, it doesn't affect the response. Sorry, I misspoke really when I said Power BI, everything in my answer relates entirely to Power Query.
So the transformation you're talking about, this would be done within the Transform Sample File query to ensure it is applied to all imported files. You may need to click up the APPLIED STEPS list a bit to see at which step PQ assigns the top row to a column header, but it should be fairly easy to identify and add a step before it that deletes the top row.
Pete
7 Replies
Hi Anonymous ,
In your first screenshot, the column that has the null dates in it is called [APRIL PRODUCTS] and shows April dates. I doubt that the appropriate column in the February file is called [APRIL PRODUCTS] so Power BI can't append these two columns together. I suspect that further along in your table there will be a column called [FEBRUARY PRODUCTS] that only contains Feb dates and no Apr dates.
In your second screenshot, contrary to the above, it appears as though the column in both tables was named [Delivery Date], so Power BI would be able to append these fine.
When you import, I would recommend the following steps:
-1- Use the Folder Connector on the folder where all your monthly reports get saved and select Combine & Transform.
-2- Filter the top-level folder table in your final append query, probably on [Date created], to get the latest six dates, or however you need to filter to dynamically get the appropriate files to be appended.
-3- In your Transform Sample File query that gets automatically generated, ensure that you change any file-specific column names to generic column names. You may need to be a bit clever with this as you don't want to hardcode the specific column name that you're changing from (as this won't work on other files with other column names) but if you search for "Dynamically change column names", you'll find a number of simple ways to do this.
Once you've created generic column names in your sample file, Power BI will apply these transformations to all of the files you've filtered in step -2-, and will append them as required.
Pete
Hi Pete,
This query relates to Power Query, im not importing into PowerBI. Does this impact the response?
Regardless your tips have helped me isolate the issue and it relates to the source data. In the source data the top row (row 1) is a "merged" cell with the text "April Products". When importing into power query it seems to be recognising this as the date column name, which is causing issues representing the other months dates.I ran the data with the top rows manually removed (ie "xx month products" removed) from all monthly files which seems to have fixed the problem!
I guess my question now is, using the import "From folder" function, can I somehow tell power query to ignore the first row of all of these reports before it imports the data?
Thank you for your help so far!
No, it doesn't affect the response. Sorry, I misspoke really when I said Power BI, everything in my answer relates entirely to Power Query.
So the transformation you're talking about, this would be done within the Transform Sample File query to ensure it is applied to all imported files. You may need to click up the APPLIED STEPS list a bit to see at which step PQ assigns the top row to a column header, but it should be fairly easy to identify and add a step before it that deletes the top row.
Pete