Forum Discussion

CMC's avatar
CMC
Helper I
4 years ago
Solved

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. C...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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.

CMC's avatar
CMC
Helper I
4 years ago

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

 

Choice 2Choice3

 

I need the column to show

 

Choice 2

Choice 3

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    When extracting select Extract to Rows.

    • CMC's avatar
      CMC
      Helper I
      4 years ago

      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?

       

       

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        Do you need this kind of output?

        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"