CODEWITHSUNDEEP
excelsharepointpowerquery
Marpa
Marpa

Reputation: 11

Load SharePoint Online list (with lookup columns) data from Excel via PowerQuery

I am new to power query but not to SharePoint. What i want to do is show in excel what i have in SharePoint. I have created a very trivial example.

This is my SharePoint list: SharePoint List

I have 2 columns. Title (default column) and LU1 (a lookup column to an external list, multi value enabled)

Now, in Excel Data > Get Data > From Online Services > From SharePoint Online List I tried in two possible ways:

  1. 1.0
  2. 2.0 (Beta)

1. 1.0 after connecting, choosing the LU1 list and selecting the two columns, I click on the icon at the top right of the OData_MD1 column. After a few seconds of "loading column names" ... "no columns were found" no columns were found

2.0 (Beta) after connecting, choosing the LU1 list and selecting the two columns, I click on the icon at the top right of the MD1 colums. Now i can "Expand to New Rows" or "Extract Values...".

If I Expand to New Rows I Expand to New Rows the column values go from "List" to "Record" so I click again on the top ricght icon and I can select the field to show Select lookupValue but the rows with multiple values in the MD1 column are repeated, and this is not the result I want splitted rows

So I Extract Values I choose to separate the values with commas comma separated .... but I receive "Error" :(

Errors

what am I doing wrong? many thanks in advance

Upvotes: 0

Views: 1739

Answers (1)

Marpa
Marpa

Reputation: 11

I found the solution. I changed the function from:

= Table.TransformColumns(#"Removed Other Columns", {"MD1", each Text.Combine(List.Transform(_, Text.From), ","), type text})

to:

= Table.TransformColumns(#"Removed Other Columns", {"MD1", each Text.Combine(List.Transform(, each Text.From([lookupValue])), ","), type text})

Of course it is strange that they put the functionality ready and selectable but then it goes wrong...

Upvotes: 1

Related Questions