Retrieving data from a table within a cell

[alvenn]

Hi everyone!

I am new to PowerBI and trying to find my way around. I have a problem maybe some of you can solve. I have one main data data source which is a Sharepoint List, let's call it A. This list has a number of lookup fields into another Sharepoint list, let's call it B. One of those lookup fields in list A can store as value several records of B (list field).

When importing list A into PowerBI, the regular lookup fields appear in the transform data screen as "record". When clicking on the header of the column, PowerBI offers me the possibility to select within all the columns that are stored in list B, as showed below, and I can select the information I would like to display in my PowerBI report

However, when I try to do the same thing on the field whose information is marked as "Table", after a long time PowerBI reports that no columns were found.

If I click on the "table" (as it is a hyperlink), I get the following message (I removed the connection info to my sharepoint)

My best guess is that the default method used to retrieve the data is valid for records, but not for tables. But I have to admit I have albsolutely no clue how to solve this. I have been looking for 2 weeks in the forum and I don't recall to have found anything answering this problem.

Can anyone help me to be able to get this data into my PowerBi report?

Thanks in advance

[alvenn · Accepted solution]

Thanks for the answer, but did not work either.

In the end, tinkering a bit I find a solution / workaround. I noticed the "Table" fields have another field with an ID, corresponding to the entry in the table. So I created a new table ("C") and performed a query based on that ID to retrieve the data I was looking for in a column. This also has the advantage of not creating new records in my table "A".

In case this is of interest to anyone, this is the code I created to get the name of the person, stored in Table "B":

Name = LOOKUPVALUE('B'[Name],'B'[Id], 'A'[TEBOberversLookupId])Name = LOOKUPVALUE('B'[Name],'B'[Id], 'A'[TEBOberversLookupId])

[christinepayton]

Is the field that is having the issue a person-type column? If it is, and if you used the 1.0 connector (which is the default), I would try a new query and instead use the 2.0 connector - it's a radio selection option when you connect to the site URL in the connection step. There is a bug with the 1.0 connetor where it is sometimes can't expand person columns properly. 

[alvenn]

Thanks for the reply, but no, it is not. It is another List I have with the names and phones of the people.

But in any case, I am willing to try the connector suggestion... can I ask how to do so?

Thanks!

[christinepayton]

Yeah, you just connect with the same connector but in the connection step where you enter the site URL there is a radio button that lets you select 1.0 or 2.0. 2.0 is a lot faster, too, so it's generally better to use it--