cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CMC
Helper I
Helper I

Extract List to new Column

I'm pulling some data from Sharepoint Lists using a Dropdown, when the data come into Power BI it looks like the below, whats the easiest way of creating a new Column, I need all of the data , e.g. Choice 1, Choice 2, Choice 3

 

CMC_0-1655361846360.png

 

1 ACCEPTED SOLUTION

Just leave code part. I think Extract values part will be very easy for you (it will generated required code automatically)

click the doubled edge arrow and Extract values.

Choose custom and then select Concatenate using special characters - Insert Special Characters - Select Line Feed - OK

Vijay_A_Verma_0-1655464849761.png

View solution in original post

15 REPLIES 15
CMC
Helper I
Helper I

I can convert the list to  a table but that then over writes all of the data in the query and only the table is viewable.  I could duplicate it then create the table but then the relationships are lost.  So if the Dropdown Column list can be converted into  a new column that is the best solution.

So when you have a list containing {"a","b","c"} and when you extract it to a column, it comes as a,b,c in a single column. You need not split it.

But I think I am not able to understand the exact problem. If you can tell me by taking an example of a list and what output do you need, I will be able to help out.

When I do the extract the column is updated to reflect:

 

Choice 2Choice3

 

I need the column to show

 

Choice 2

Choice 3

When extracting select Extract to Rows.

1.png

That will work but the problem then is that then introduces rows with duplicate content, how can I get the data all within the same row?

 

CMC_0-1655376450997.png

 

Do you need this kind of output?

1.png

If yes - See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIxNVWK1YlWcgKyDY3MLZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Sales = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Choices", each if [Customer] = "A" then {"C2","C2","C3"} else {"C4","C5"}),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Choices", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text})
in
    #"Extracted Values"

Yes the only column I'm interested in the Choices Column and to get the data extracted, there is requirement to look at the customer specifically , that code you are looking for specific values, how do you make it generic to just create the new column and data?

 

CMC_0-1655461913385.png

 

Can you open one list and post here the screenshot and then you can describe what output you want...

Ok sure..

 

1. Extract the values from the list field,  which could be anything, e.g. Apple, Pear, Carrot

2. Create a new colum with those values so it looks like

CMC_1-1655462832203.png

 

 

 

 

CMC_0-1655462738584.png

 

That is what I had done...You need to insert this statement in your code where you need to replace #"Added Custom" with previous step.

To do this, right click on last step of your query - Insert step after (now in formula bar, it would show your previous step which you should copy) and paste the below one into Formula bar and replace #"Added Custom" with your previous step

= Table.TransformColumns(#"Added Custom", {"Choices", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text})

Now, if you are not able to do so, then click the doubled edge arrow and Extract values.

Choose custom and then select Concatenate using special characters - Insert Special Characters - Select Line Feed - OK

1.png

Sorry you lost me there, can you post the full code?

Just leave code part. I think Extract values part will be very easy for you (it will generated required code automatically)

click the doubled edge arrow and Extract values.

Choose custom and then select Concatenate using special characters - Insert Special Characters - Select Line Feed - OK

Vijay_A_Verma_0-1655464849761.png

thats the one!  Sorry we got there in the end! Many thanks

Vijay_A_Verma
Super User
Super User

Simplest way is to click double edged arrow at the top and extract the data into a new column which will have combined text. Then you can split the column.

That will work but the issue is if you update the DropDown value in Sharepoint, e.g. Choice 4 it creates another value which BI doesn't know anything about..  So ideally its a new column with all the data

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Kudoed Authors