Lookup value and pass it out to create a CSV

[J_Taylor]

I am building a flow that takes data from two Excel tables (Class List and Class Code) and, through several steps, creates a CSV. I slightly more detailed version would be:

1. Takes each class name (from List).
2. Gets [through a filter] the codes for the class.
3. Passing these out to create another array.
4. Using the array to create a CSV.

So far, I have completed steps one and two, but I am having issues after that. 

TL:DR How do I add static and dynamic content in the Map of a 'Select'? Static content includes double quotes and backslashes - which seem to be disallowed. (Main question is emphasised towards the bottom of the post.)

Let me take you through what I have so far.

I have two Excel workbooks (List and Codes). These are then modified in 'Select' to create two arrays. I need them to be modified because I can't compare [LIST.xlsx]List_Sheet!$A$A with [CODES.xlsx]Codes_Sheet!$A:$A because the codes are not unique. To get around this, I combine [in the 'Select' map] two columns from Codes to create a unique item. Apart from a minor question below the image, this seems to be working fine for me.

As a side question regarding the "Year" mapping (second from the bottom): is it possible to create an expression like "right(item()?['YEAR'],2)"? I tried this, but it gives me an error. The purpose of this is to change from 2022 ('yyyy') to 22 ('yy').

Now that I have the two arrays, I use 'Filter Array' inside an 'Apply to each' to find the Code from each Class Name (i.e., from List). This also works without issues (I suppose).

After this, I start to have issues. In my mind, the next steps should be easy. However, it is clear that I do not understand the logic (of Power Automate) to construct it. If I may, below are the steps that I think should happen.

1. The filter results in one object/item (if my terminology is incorrect, please let me know). If this were Excel, there would be one row. 
2. Since there is only one object, I should be able to take each corresponding column and pass that to anything else using the Dynamic Content.
3. All of this content which is easily accessible through Dynamic Content should be usable to create a new array and, finally a CSV.

Unfortunately, the items (columns) are not selectable through Dynamic Content. Therefore, I feel like I am trying to hack (inefficiently and probably incorrectly) together a solution.

The first step of this potential mess is to parse the result of the filter to a JSON. This seems to enable me to select the items/columns through Dynamic Content. 

Let me explain a little about the next section. The next piece is an 'Apply to each' (with a nested 'Select'). I didn't want this - I just wanted a 'Select' to create an array with which I can create a CSV. However, this was not to be.

I have briefly looked up about why 'Apply to each' is often automatically added. The best explanation I found was that if there are square brackets "[...]", it means it is an array (?) and if it is an array an 'Apply to each' must be present (since there is a possibility of more than one object (?)). Either way, it doesn't matter if there is only one object (?) after the filter, I need an 'Apply to each'. I tested using a 'first()' expression to bypass the requirement for 'Apply to each'. However, there was always an error saying something like "a string (?) cannot be used in the 'from' and therefore is invalid." At the end of the day, I don't care what I need to do, as long as it works. 

[Main problem and question]

The 'Select' has some issues that I have not been able to fix. It seems to be my last hurdle (I hope) before I can get my CSV. The issue arises when I need a string with references to items intermingled. The string is a dynamic rule that I will put into AzureAD. It needs pieces of the Class Code so the rule is customised for the Class Name. Below, I will summarise the rule:

(user -match "@.*School.*\.au") and (user.extension -match "\b22\.\b(7ATL\.)[\w-]*B")

Or, replaced with the pieces of School Code:

(user -match "@.*[VARIABLE(School)].*\.au") and (user.extension -match "\b[ITEM(Year)]\.\b([ITEM(Grade_Code)]\.)[\w-]*[ITEM(Identifier)]")

I run into issues because I cannot put double quotes or backslashes in the map (there may be other issues). I tried to get around this by putting the static content in a single concat() expression. However, when I did this, extra backslashes were added for some reason. This is the main issue that I am having. If anyone is able to help me get this static and dynamic content together under the Map of a 'Select', I would be extremely appreciative.

After this, I hope that I can just put a 'Create CSV table' and all the codes from the first 'Apply to each' will be entered in. However, I will test it once the above issue is fixed.

I realise I have only included the screenshots of the Flow, not of any content. I have saved a copy of the content in this link if you need to refer to anything in particular.

[J_Taylor · Accepted solution]

I found a solution. 

Instead of passing everything to an array, I used variables to create the values before passing them to a string. From the string (which had formatting of a json), I passed it to a json and then out to the CSV. 

I am having other issues (all results not being generated), but this is for another post.