Forum Discussion
Date data showing as "null" despite source data having values
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
Ok. The code you've provided here looks like it's from the final, appended, query. You'll need to make the transformation change in the Transform Sample File query for it to be applied to all imported files prior to appending.
Let's try this. It's a bit fiddly but step --1-- should provide me with exactly what your data looks like when it FIRST come into Power Query, and step --2-- will provide me with the current sample transformations that I can adjust to do what you need:
--1--
Just import your April file into PQ by connecting directly to the Excel file (I'm assuming that the April file is currently your sample file).
Once imported, delete any steps that have been auto-generated by PQ after the navigation step (promoted headers, changed types etc.).
Filter the table to just top 20 or 30 rows.
Click at the top left of the table in PQ and select 'Copy entire table':
On the Home tab, select 'Enter Data' and paste your copied table straight in there.
Once you've applied your new 'Enter Data' table and it's loaded, copy the entire M code for it and paste it into a code window here ( </> button).
--2--
Select your Transform Sample File query, copy all the Mcode and paste into a code window here.
Pete
Pete, you are a legend, I found the transform file (it was hiding in the Helper queries section) and removed the top row at this stage which has solved my problems. Appreciate all your help.
For anyone reading the thread, here is where you remove the row.
No problem, glad it's sorted.
Don't forget to give a thumbs-up to any answers that have helped you along the way 👍
Pete