Uploading data issue

schwartz

New Member
Joined
Dec 7, 2014
Messages
3
Just starting to use Power Pivot so this may be an easy question. Basically, I have a fact table and then a lookup table with just IDs and Names. My issue is that 2/3 of the IDs are just numbers and the rest start with the same abbreviation, for example "RO1334, RO2333, RO4444, RO8888" and so on. All the IDs with abbreviations do not import. I am assuming because Power Pivot see all the IDs with ROxxxx as duplicates, the IDs are not imported. Is there a work around? How do I get all the IDs imported?

Thanks for the help
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
If you are missing rows in the Fact table, it is likely that Excel is determining the data type of the column based the IDs that are numbers, and not bringing in the ones that look like text. Go into the PowerPivot window and select the Fact table. Click on the column with ID and in the Ribbon under Formatting, look for Data Type. This is probably showing Whole Number.

I am assuming the source of the data is an Excel / Text / CSV file rather than a link to a database table, is that correct?

If the above is correct, my suggestion is to create a 2 line file based on the Fact table, with the header and a single data line, with RO1334 as the ID (if any of the other columns are getting incorrectly tagged, ensure the data in each column represents the data type you want for the whole table going forward.

At this point, if you have not done much, start a new file and import this small Fact file. Once that is imported, then Edit the connection and point to the main file. Excel should remember the data type for ID to be Text and import all the rows.

You can solve this with Schema.ini files, but this might take a bit of reading to understand how to approach it.

If that does not seem to be the issue, please provide more details.

Cheers
 
Upvote 0
I am currenting importing two excel files, a large fact file and a two column lookup table with the same headers as the fact table. I will try what you described.
 
Upvote 0
Jafa nailed it. The only other option I can think of is to use Power Query, which is just better at importing text files.
 
Upvote 0
Jafa1970, I followed your advice which really helped to get all of the data ready and imported correctly. Thank you for the help
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top