Add associated column data to a CSV

[BCBuizer]

Hi All,

I'm trying to build a flow that frequently creates a report in a CSV from data that is stored in a SharePoint list. The data for one column (Unit) is not stored in the same list that has the data, but in a separate list called SubUnits where the SubUnits are mapped against the Units, with both columns as text. Ideally I want to add an expression to the "Create CSV table" action, that will populate the UNIT column in the CSV:

[Mira_Ghaly]

@BCBuizer 

Can you please share a sample of your sharepoint list so I can try to replicate at my side?

[BCBuizer]

Hi @Mira_Ghaly ,

The transaction list (used in the Get Items action):

Title (Text)	SubUnit (Text)	Etc.
Transaction 1	London
Transaction 2	Paris

The SubUnits list (from which I need the Unit value, based on the value of the SubUnit):

Title (Text)	Unit (Text)	etc.
London	UK
Paris	France
Manchester	UK

In PowerApps I would use something like: LookUp(SubUnits,Title=ThisItem.SubUnit).Unit, but I don't know what the Power Automate expression equivalent for this is.

[KvB1]

Only way I would know how to do this is to create a for all loop, within it fetch items from the second list using the subunit field as an odata filter query. 

Ofc it would be much nicer to not need a loop, so i hope someone knows a way ^^