Extract values from a list and combine them into one column - another SharePoint data source problem

Power Query
1

Good evening

I am connecting to a SharePoint list, that contains a lookup column which contains values from a column in another list.
I would like to expand the values in the lookup column so it holds all relevant values for a particular row in one column.

It seems like Power BI should be able to do this using the user interface, but I keep getting an error message.
I have recreated the issue using “dummy SharePoint data” as shown in the image below.

Expand lookup value1
Expand lookup value11634×341 55.2 KB

When I expand the lookup column to new rows, I get each lookup record. When I expand the fromLookup field I then get the required lookup value, but not in one column as desired.

When I attempt to expand the froomLookup value into columns, with or without a delimiter I get the error message shown below in image 7.

Expand lookup value2
Expand lookup value21737×337 48.2 KB

I think the issue lies within the Text.Combine(List.Transform(_, Text.From) “;”) part of the code as shown in the formula bar, but I have not been able to find a way to change the m-code to access the required field within the record without getting other similar cryptic messages.

The desired result would be to have two fields at the end i.e. Identification and fromLookup, with fromLookup containing delimiter separated text values of the lookupValue.

So in the example shown it would be:
Identification = First item
fromLookup = 02 - Description B; 01 - Description A; 03 - Description C

Can one of the experts suggest how to fix the code?

Thanks.

2

Hi @Mo.jo.jo,

First of all. Great job on identifying the issue and clear problem description :+1:
Give this a go. Just paste it into a new blank query.

let
    Source = 
        #table( { "ID", "FromLookup" },
            {
                { "FirstItem", { [lookupID =1, lookupvalue = "02 - Description B"], [lookupID =2, lookupvalue = "01 - Description A"], [lookupID =3, lookupvalue = "03 - Description C"] }}
            }
        ),
    GetValues = 
        Table.AddColumn( Source, "Result", each 
            Text.Combine( 
                List.Transform( [FromLookup], each _[lookupvalue]? )
                , "; " 
            )
        )
in
    GetValues

.
Note you could wrap the field access in a Text.From
Text.Combine( List.Transform( [FromLookup], each Text.From( _[lookupvalue]?) ), "; " )

image
image908×94 6.56 KB

I hope this is helpful

1 Like
3

@Melissa
You make it look very simple :clap: :clap:
Can you please explain why the UI approach/m-code failed so misearably? Did it every stand a chance of working? I tried to tweak it in multiple ways.

Feel free to let me know when you are ever on vacation from the forum and I will take vacation at the same time or I just stop bothering doing Power Query related stuff during that period :face_with_raised_eyebrow:

Thank you.

4

Hi @Mo.jo.jo,

Another approach leveraging the UI would involve expanding the list to expose the records, extracting the record field values of interest, perform a Group By, to get the list with values and extracting those.

I think you were very close to solving this on your own, when you compare the code in image 7 to the final result, only real difference is the field access operator. And I think that’s also something we can never expect the UI to “guess” for us :wink:

1 Like