PowerQuery: Column of Data and Lists

[Tuta]

all -- playing with PQ a little more.

I have some web data, that has a column of cells that contain either a list or just a single piece of data.

What I want is wherever there is a list, I will always want the first item, if no list, just the data that sits in that cell of the table.

Here is the page I'm playing with. The column in question is the "Title" column in the timeline table.

thank you!

 

[Matt Allington]

I don't understand the problem. Can you please explain what the issue is.

 

[Tuta]

Hi Matt -- thanks for the time.

So, when I paste this link into Power Query, select Table 1, and click edit query -- I see in the TITLE column something similar to this:

List
List
List
List
Catalyst
List
List


Well -- whenever there is an ordered list in the cell of web table -- I only want to see that first item (which is the real title of the book)

So -- when I have a column of data comprised of both LISTS and text, how do I tell PQ to return only the first list item where a list exists or the text if no list is present....

I hope that explains it a little clearer.

Thanks!
john

 

[billszysz]

Hi Tuta,
Try this code below ( The last line is that what you want - if i understood correctly)

let
    Source = Web.Page(Web.Contents("http://starwars.wikia.com/wiki/Timeline_of_canon_books#js")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Year", type text}, {"", type text}, {"Title", type any}, {"Writer(s)", type text}, {"Released", type date}}),
    OnlyFirst = Table.TransformColumns(#"Changed Type", {{"Title", each if _ is list then _{0} else _ , type text}})
in
    OnlyFirst

Regards

 

[Tuta]

Sorry for the long delay -- such is life.

Bill -- that worked beautifully and was exactly what I wanted to do. Examine each cell's content within that column, return the first item if a list, or if not a list, whatever was there.

Thanks so much!