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

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.