Share point list & Power query related question

Question

Hi there,

MY requirement is to connect to a Sharepoint list via Power query. I could connect to the Sharepoint list. On the query it shows many columns that I cannot see on the Sharepoint list. Also I cannot see some columns I can see on Sharepoint list. How should I resolve this issue?

Thanks,
Imalka Jayalath

Answer 1

Hello Imalka Jayalath,

As I understand you have an issue with a Person & Group column when extracting to Power Query. Technically it happens because Person & group column is a Lookup column. Lookup fields are a lookup into another SharePoint list. Internally, the SharePoint item stores this as an ID and display value, but Power Query gives you access to all of the properties of the related item as a one-to-one relationship. Essentially, what you can do is to flatten that relationship by incorporating the related item’s attributes.

If you want to extract a query as it looks in your SharePoint list I would recommend you to use Implementation 2.0 (Beta) Default > choose your list and click Transform data. Here you may customize your query before exporting to Excel worksheet

---

Note: the data in the column is presented as Table, if you click on the cell you can see all data that this table contains

You can click the expand button and choose the values you want to include to your query:

Best Regards,

Igor

Answer 2

Hi Igor,

Thanks for the reply. It's good information for future.

But Im trying to connect to SharePoint list through Excel. ( Data --> Get Data -->From online services --> From SharePoint Online List ).

I have following default columns on the list :

Column (click to edit)	Type	Visible on the Sharepoint	Visible on the Sharepoint
Sl #	Single line of text
Modified	Date and Time
Created	Date and Time
Category	Choice	Visible
Training Title Code No	Choice	Visible
Venue	Choice	Visible
Start Date	Date and Time	Visible
End Date	Date and Time	Visible
Trainer	Person or Group	Visible , There will be multiple records for 1 row. Means multiple trainers allocated to do a training on a particular date
Non Training Title	Choice	Visible
Room	Choice	Visible
Pre-Training Status	Choice	Visible
Post Training Status	Choice	Visible
Notes	Multiple lines of text	Visible
Month	Calculated (calculation based on other columns)	Visible
Day of the year	Calculated (calculation based on other columns)
Week No	Calculated (calculation based on other columns)	Visible
test board 2 choice1	Choice
Created By	Person or Group
Modified By	Person or Group

Below are the visible columns on the list,

When I connect through default , I get the following columns,

FileSystemObjectType	Id	ServerRedirectedEmbedUri	ServerRedirectedEmbedUrl	ID.1	ContentTypeId	Title	Modified	Created	AuthorId	EditorId	OData__UIVersionString	Attachments	GUID	ComplianceAssetId	field_1	field_2	field_3	field_4	field_5	TrainerId	TrainerStringId	NonTrainingTitle	field_6	field_7	PostTrainingStatus	field_10	Month	Day of the yea	Week No	test board 2 c

When I connect through Beta, I get the following columns,

Month	Week No	Category	Training Title Code No	Venue	Start Date	End Date	Trainer	Non Training Title	Room	Pre-Training Status	Post Training Status	ID	Content Type	Sl #	Modified	Created	Created By	Modified By	Version	Attachments	Edit	Type	Item Child Count	Folder Child Count	Label setting	Retention label	Retention label Applied	Label applied by	App Created By	App Modified By	Compliance Asset Id	Notes	Day of the year	test board 2 choice1	Item is a Record

Problem is I have multiple trainer names on the "Trainer" column. I do not get that in the Beta version. Where there are trainer / trainers assigned I get [List] on the Excel.

How to overcome that?

Thanks!

Answer 3

Hello Imalka Jayalath,

Thank you for updating a scenario.

May I know if you tried to extract the query data from SharePoint list with Export to Excel?

You may also try to get data select 2.0 (Beta) under Implementation if it isn't already selected. With this update to the connector, we're making available two different views for the same data: All and Default.

The All view includes all user created and system defined columns.

The default view is what you'll see when looking at the list online in whichever view you've set as Default in your settings.

Please let us know if this information is related to the issue you are encountering.

Best Regards,

Igor

Answer 4

Hi Thanks for the reply.

I am using Get data from SharePoint online list. The list sits on the MS teams channel . I access the list via SharePoint online list because I require to do some regular analysis.

There are some columns that were brought in, I cannot see where this data is coming from. The columns I created do not have the heading. Heading names come as Filed_#.

It will require lots of cleaning and transformation in order to convert it to a workable data set.

What am I doing wrong or is this how the functionality is? Let me know.

Thanks!

Answer 5

Hello Imalka Jayalath,

Welcome to community forum.

Per your description you see many columns that are not included in SharePoint list. You also cannot see some columns in query that are included in SharePoint list.

May I know what kind of columns are not in your Power query?

May I also know how were this query created? Did you Export a SharePoint list to Excel within a browser or connected a list to Power Query via Get Data feature in Excel?

You may also read this article to find more detailed information Power Query SharePoint Online list connector - Power Query | Microsoft Docs.

Best Regards,

Igor