Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Jngd94
Regular Visitor

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". 

Jngd94_0-1689141888414.png

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: 

Jngd94_1-1689142567493.png

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

 

 

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

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

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
BA_Pete
Super User
Super User

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

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.

Jngd94_0-1689149661897.png

 

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! 

 

Jngd94_1-1689149734996.png

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

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':

BA_Pete_0-1689153200337.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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. 

Jngd94_0-1689196847091.png

 

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors