Date data showing as "null" despite source data having values

[Jngd94]

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

[BA_Pete · Accepted solution]

Hi @Jngd94 ,

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

[BA_Pete · Accepted solution]

Hi @Jngd94 ,

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

[Jngd94]

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!

[BA_Pete · Accepted solution]

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

[Jngd94]

Thank Pete, appreciate your help.

Any tips and tricks on how to remove this? I've tried using "remove rows" but it just seems to be removing the row with the Delivery date, item description, quantity ect. Its almost like power query isnt recognising the "April Product" row as the first row?

I've added the code if this helps:

let
Source = Folder.Files("C:\Users\rgebert\Work Folders\Documents\My Files\2023 Reconciliations ORICA"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"APRIL PRODUCTS", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}})
in
#"Changed Type"

thanks again

[BA_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

[Jngd94]

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. 

[BA_Pete]

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