I have a power pivot import from another excel file that for some rows skips a column and moves the data left (ie into a different column)
The power pivot query is set up to 1) read all the tables in an excel file (30-50 tables) 2) filter the tables based on their name 3) combine them together into one large table by inserting and expanding the column names.
The tables all have similar columns, and 2400 rows work, but 82 rows show up as errors. On the error rows it skips a column and places the data to the right left one column on about 12 of the tables for about 6 columns.
I have tried deleting those rows from the source table (in case it was a data error) but it just does the same thing to the next rows (same count). Seems to always be rows at the beginning of the table.
I have tried renaming the table, recreating the table, deleting all the data in the table. And the same rows keep not being imported. I can import the table individually with out errors. I have rebuilt the
Some tables only have 1 row where this happens some have up to 10 rows.
It is really weird and the same issue across all tables that have an issue. It would be better if it was some sort of normal error, but placing data in diff columns is very weird.
Yes i should use a db, but excel and power query was supposed to be a quick and dirty proof of concept.
----edit Nov 17 ---
Here is my power query, I will check the rows above for errors as suggested and report back.
let
Source = Excel.Workbook(File.Contents("P:\Daily Truck Sheet\TimeTrack\testing\Copy of TimeTrack.xlsm")),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "p2014")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Descending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each pleasework2 ([Name])),
#"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Description", "ClientCode", "Qty", "UOM", "Total", "FieldA", "FieldB", "project", "Unit Price", "tc/Ref", "EE", "Hours", "QTYcompleted", "Started", "Finished", "Complete","STREET ADDRESS","STREET NAME","City","CONDUIT / DROP / STREET CROSSING?","LENGTH OF CONDUIT","LENGTH OF DROP","DATE PRE-FIELDED","AS BUILTS / PICS RECEIVED","DATE COPIED TO CD"}, {"Description", "ClientCode", "Qty", "UOM", "Total", "FieldA", "FieldB", "project", "Unit Price", "tc/Ref", "EE", "Hours", "QTYcompleted", "Started", "Finished", "Complete","STREET ADDRESS","STREET NAME","City","CONDUIT / DROP / STREET CROSSING?","LENGTH OF CONDUIT","LENGTH OF DROP","DATE PRE-FIELDED","AS BUILTS / PICS RECEIVED","DATE COPIED TO CD"}),
#"Removed Columns" = Table.RemoveColumns(#"Expand Custom",{"Name", "Kind", "project", "Data"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Estimated Hours", each [Total]/75),
Rounding = Table.TransformColumns(#"Added Custom1",{{"Estimated Hours", each Number.Round(_, 2)}}),
#"Added Custom2" = Table.AddColumn(Rounding, "Hours % Complete", each [Hours]/[Estimated Hours]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "QTY % Complete", each [QTYcompleted]/[Qty])
in #"Added Custom3"
-----Nov 18-----This fixed the issue---------
- I copied the data out of ALL the rows of the tables that were causing problems
- pasted the values outside of the table
- deleted all the rows of the table using ctrl - (so the table only had one empty row in it)
- saved workbook
- copied and pasted values into the table letting excel insert the rows into the table
- saved xls and reran power query = problem solved
No idea what kinda wacky formatting was in the offending rows causing the issue.
