cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rujimenez Frequent Visitor
Frequent Visitor

Import Access fields that allow multiple values from lookup

When importing an Access table, fields that allow multiple values from lookup tables are missing.  Is there a workaround or am I doing something wrong?

1 ACCEPTED SOLUTION

Accepted Solutions
rujimenez Frequent Visitor
Frequent Visitor

Re: Import Access fields that allow multiple values from lookup

There are three fields which contain multiple values.  I solved the problem creating three Access queries on the primary key, date, and each field value (field.value)  individually.  I downloaded these queries in BI which gave me the information I needed in the correct format.  Thanks for your assistance.

6 REPLIES 6
Super User
Super User

Re: Import Access fields that allow multiple values from lookup

Could you be more specific about the events and the outcome?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
rujimenez Frequent Visitor
Frequent Visitor

Re: Import Access fields that allow multiple values from lookup

I used the "Get Data" function to identify various Access tables from a database.  All fields were correctly imported except three from a single table.  They are completely missing in Power BI.  I determined that the unique feature of these three fields were that each had the following defintions in their structure.  In the "General" tab, no "Indexed" row is displayed.  This is caused by the fact that in the "Lookup" tab, the "Allow Multiple Values" is set to "Yes".  Data entry into this Access table is made through a form, so changing the structure is not an option.

Super User
Super User

Re: Import Access fields that allow multiple values from lookup

Is this a refresh of a table previously loaded, or an initial load. A brand new table?  I have never heard of a partial load failure on a brand new table load. What happens if you open a blank new workbook and just import this table?  Does it work?  If you have previously imported a table and used columns to create relationships (e.g. In the one side of a relationship) and then you refresh the table and the data columns are no longer unique, then I guess someing Bad like this could happen. It certainly won't work, but I'm not sure how power bi will trigger an error. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
rujimenez Frequent Visitor
Frequent Visitor

Re: Import Access fields that allow multiple values from lookup

Although they exist and are populated in the Access table, BI does not import them on the initial load nor any refresh.  Since they don't exist in BI, they aren't used in relationships.  BI doesn't trigger an error - the fields just don't exist.  I didn't realize they weren't there until I needed to reference them in a report.  These fields are vital to the report so I need to find a solution as soon as possible. 

 

Super User
Super User

Re: Import Access fields that allow multiple values from lookup

OK, I think I understand.  So it sounds like these columns are not scalar values, but instead a table of values.  Is that correct?  Can you write a query in Access that expands the data into multiple columns or other tables?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
rujimenez Frequent Visitor
Frequent Visitor

Re: Import Access fields that allow multiple values from lookup

There are three fields which contain multiple values.  I solved the problem creating three Access queries on the primary key, date, and each field value (field.value)  individually.  I downloaded these queries in BI which gave me the information I needed in the correct format.  Thanks for your assistance.