Forum Discussion
Date data showing as "null" despite source data having values
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!
Super User
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
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
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.