List.Select - Power Query M Language

[Anonymous]

Hi community!

I'm trying to return values from a column ([ListColumn]) which contains a comma separated list, using M code. The particular entries I want to return are ended with a colon. For example, the (text) list might look like this: "Command:, process, management, Leadership:, process". I want to return "Command:, Leadership:"

I have tried the following code, but just get an error:

AddColumn = Table.AddColumn(PreviousStep, "New Column", each List.Select([ListColumn], Text.End(_ ,1)=":" )) 

Any pointers? I'm new to using list functions.

Regards

Will

[pqian]

 @Anonymous you are missing the "each" in the condition

AddColumn = Table.AddColumn(PreviousStep, "New Column", each List.Select([ListColumn], each Text.End(_ ,1)=":" )) 

[Anonymous]

Sorry, that was an omission.

Even with the code you suggest, I get a column full of "Error". Is it something to do with the [ListColumn]? The entries in it are literally like this: 

Weather:, Weather, Conditions:, Arrangement/Placement of items

Will they be recognised as a list because of the commas, or do I need to do something to process them a bit first?

Many thanks,

Will

[Anonymous]

I have also tried retrieving the positions of the various text strings (which end in colons) using the following:

ListPositions = Table.AddColumn(PreviousStep, "New Column", each List.PositionOfAny([ListColumn], {"Command:","Management decision:"}, Occurrence.All))

...but this also leads to a column of "Error", unfortunately.

Will

[ImkeF]

What does the error say?

[Anonymous]

Hi Imke,

It says...

Expression.Error: We cannot convert the value "Command:, processes" to type List.
Details:
Value=Command:, processes
Type=Type

My column is just a text string, but I don't know how to convert it to a list???

Will

[ImkeF]

Hi Will,

you use Text.SplitAny for this task:

AddColumn = Table.AddColumn(PreviousStep, "New Column", each List.Select(Text.SplitAny([ListColumn], ","), each Text.End(_ ,1)=":" )) 

[Anonymous]

Thanks! I'll try that.

Do you know of any good resources for explaining to me what lists are and how to use them?

Will

[ImkeF]

Have a look at the quick reference: https://msdn.microsoft.com/en-US/library/mt186367.aspx

and browse your way through. It gives a good overview of the object/value types.

How to use them? Difficult to say. M provides functions specificly for each of type of value where you normally find what you need. But in cases where not (like in your example) it can make sense to transfer your value to a different type because you find the fitting function there.