Possible to reference and store column values outside of transform functions?

[Anonymous]

Hello, I am wondering if it is possible to store values outside of transform functions such as Table.AddColumns or Table.TransformColumns. See below for an example of what I mean: 

[Dept ID] is a column that contains a mix of text and numbers (eg: 33, 309, A371). I am trying to convert these to a standard format that is 4 characters long and begins with a letter (eg: R033, R309, A371). As such, I have two conditions that check whether the first character is a number and then checks if the text is three characters long, then makes the necessary adjustments. 

I understand that all of these logic checks can be done inside the Table.Transform functions but was wondering if it was possible to store these values outside the function in something like a list that could then be referenced later. As of now the _concatenate step is returning only the last conditional value ("") so I feel I am on the right track but not exactly sure where to go from here.  

Thanks 

[AlB]

Hi  @Anonymous 

Im a bit confused. The code you show is what you're going to use as an argument to a Table.Transform( ) function or similar? You are using the keyword "each" multiple times, which would imply so but the at the beginning you have a name (#"Format Team"  as if it was a usual step in the query. That's what confounds me.  Can you share the full M code of your query, to make things clear? and perhaps explain a bit more what you need, based on that full code?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Yes the code be used in a Table.Transform() function, but I am wondering if it is possible to do the same exact thing outside of a function. Is it possible to store values in a variable using the each keyword? Or is the keyword strictly reserved for use as a parameter within functions?

And yes I'm using the #"Format Team" as a way to keep the applied steps concise. Since it involves multiple steps (adding a column, removing a column) this just puts two related applied steps into one. The _start variable refers to the previous step in the query and setting it equal to #"Filtered Rows" is just bringing in the table in it's current form. The rest of the query steps are irrelevant since I'm only concerned about the [Dept ID].

[AlB]

@Anonymous 

the each keyword is nothing but shorthand for

(_)=>

i.e., the declaration of a function that takes _ as input parameter. It is used extensively in arguments that require functions, such as the second argument in:

Table.SelectRows(table as table, condition as function) as table

 followe by the body of the function. Here is a fantastic explanation 

So, each cannot be used to store variables. That said, you can store values in "variables", of course. That is what you are already doing in your code, in each of the steps:  you are storing a value in _start another in _firstChar, etc. It is there where I fail to understand exactly what you really are asking, since you seem to be doing it already.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Great thank you for the explaination and resource. I guess my question then is if everything is working as I expected, then why I am getting blank values in the _concatenate step? Why are _firstCharCheck and _lengthCheck not true for the following examples?