Forum Discussion

Katsutoshi7's avatar
Katsutoshi7
Copper Contributor
Nov 07, 2023

Expanded lookup from sharepoint list - how to expand a lookup column that can have multiple values

Hey there,

 

The goal of this overall is to either have a sharepoint list column show the user selected lookup column value/values as pure text for easy copy paste of the whole table with data. I have not been able to do this in sharepoint, so now im approaching it via powerquery/excel

 

I have a Sharepoint List and i am pulling data with powerquery into excel - then in powerquery editor after expanding the lookup column from the microsoft list I only get the first value (if it is multiple choices it will not show the other values). Please see example.

Here is the microsoft list data with the multiple choice elever column on the far right - two person names have been choosen

 

 



Raw data form in powerquery

 

Expanded the Elever column (lookupValue) - here is the problem, it creates two rows instead of converting it into (what i prefer) the two names in the same cell with "," as seperator. Example: "(1) Anastazia, (Gy2) Isaac" should be the output for that respective row from the Sharepoint list.

 

 

 

Is this doable? Really appreciate any help and input

Regards,




excel

1 Reply

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 11, 2023

    Katsutoshi7 

    The challenge you are facing in Power Query with multiple values from a lookup column is quite common. As of the last update in Power Query, it does not natively support merging multiple lookup values into a single cell directly during the expand operation.

    However, you can try an alternative approach:

    Steps to Concatenate Multiple Values from Lookup Column:

    1. Expand the Column:
      • Expand the lookup column to extract all its values, resulting in multiple rows.
    2. Grouping:
      • After the expand operation, group the table by the common key to consolidate the multiple rows into a single row for each unique item.
    3. Custom Column:
      • Add a custom column that combines the individual values into a single cell using the M language.
      • Use Table.Group to group rows based on your key column.
      • Apply Table.AddColumn to concatenate the multiple values into a single text cell with a delimiter like a comma.

    Example M Language Code:

    Assuming your table is named Source and you are concatenating values from the column named Elever:

    Mcode:

    let
    groupedTable = Table.Group(Source, {"YourCommonKeyColumn"}, {{"AllValues", each Text.Combine([Elever], ", "), type text}})
in
    groupedTable

    This code groups the expanded rows by a common key and then concatenates the values in the 'Elever' column for each group, using a comma as a separator.

    This workaround can help aggregate the multiple lookup values into a single cell within the Power Query editor.

    Remember to replace "YourCommonKeyColumn" with the actual name of the key column used for grouping the records.

     

    NOTE: My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together. Maybe it will help you further in your project, if not please just ignore it.

     

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources