CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
Josh Willis
Josh Willis

Reputation: 115

PowerQuery expand column containing list

I have a table of orders from a data source. Each of those orders has a number of sales reps associated with them. My data source has multiple columns representing those reps. I have created a custom column that appends those names into a list. Because of how I would like to visualize and slice the data, I would like to create a new table from the existing table. The new table would contain the order number column and the list column but would expand the list column to create N number of rows based on how many sales reps were in the list column.

How can I do this using Power Query or DAX in Power BI?

Thank you!

Source Table

Order # All Reps Many other Cols
1234567 {R1, R2, R3} stuff
0000012 {R1, R2} stuff

Desired new table

Order # Sales Rep
1234567 R1
1234567 R2
1234567 R3
0000012 R1
0000012 R2

Upvotes: 1

Views: 1040

Answers (1)

horseyride
horseyride

Reputation: 21413

If the All Reps column contains actual lists then in powerquery just use arrow atop that column to expand to new rows

enter image description here

if you have actual text in there like

enter image description here

then expand it after converting as follows

 Convert = Table.TransformColumns(#"PriorStepNameHere",{{"reps", each Expression.Evaluate(_)}})

Upvotes: 1

Related Questions