Power Query returning seeming random NULL values

Jules Cole 21 Reputation points
2022-03-25T10:09:52.013+00:00

Hi. First time ever posting a question about anything but I'm stuck and have been going round in circles on this for days. I have a Power Query reading from multiple Excel worksheets in a sharepoint folder with the exact same table and formatting i.e. the source was the same for all. The query is brining back 'null' for a few values which are present in the source spreadsheet. I have deleted them and re-created them and can see no logical reason why the values aren't being pulled through. I've tried changing the column type but the value is being pulled through for others. The missing data is in teh Job (WOS) column and seems random. Please let me know if anyone can help and if so, what more do you need?

Thanks in advance.

Advanced editor code:

Source = SharePoint.Files("[REMOVED]", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "[REMOVED]/Documents/General/Timesheets/")), #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{<!-- -->{"Source.Name", type text}, {"Date", type any}, {"Type", type text}, {"Job (WOS) e.g. 1234", Int64.Type}, {"Ticket No(s)", type any}, {"In hours hours", type number}, {"Out of hours hours", Int64.Type}, {"Notes", type text}, {"Employee", type text}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Date] <> null and [Date] <> "Total")), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Source.Name", "Employee", "Date", "Type", "Job (WOS) e.g. 1234", "Ticket No(s)", "In hours hours", "Out of hours hours", "Notes"}) in #"Reordered Columns"

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,955 questions
0 comments No comments
{count} votes

0 additional answers

Sort by: Most helpful