Conditional text output based on excel table with multiple dupplicated Customer IDs

(0) Share

Report

Posted on by JB-06031119-0

I'm sure the answer is way easier than I'm making it but, I'm trying to create a flow that will iterate through an excel table like this:

Customer ID	Post Code	Account Name	First Name	Surname	Provider	Product Type	Plan Type	Pol Number
388023432	RG132DN	Tupert	Tupert	Tupertine	Currys	Playstation	Pay Full	WP2323dad
388023432	RG132DN	Tupert	Tupert	Tupertine	Currys	Xbox	Pay Monthly	Wwdw222323
238805741	RG991HP	Carlos	Adrian	Grace	Amazon	Playstation	Pay Monthly	3e23e332e
638805610	SP202NP	Joseph	Martin	Joseph	Argos	Xbox	Pay Monthly	qasw2222qw
638805610	SP202NP	Joseph	Martin	Joseph	Amazon	Playstation	Pay Full	wd3q45523
338805910	LN121SS	Anthony	Claudius	Anthony	Currys	Nintendo Switch	Pay Monthly	656yhdf
338805910	LN121SS	Anthony	Claudius	Anthony	Currys	Xbox	Pay Full	5t4f4f

The point here is that I have the same customer over multiple rows, but each row has information relevant for the conditional text I want added to a Word Document.

The text should read for example for Mr Tupert, "Dear Mr Tupert, You have purchased from currys a Playstation in a pay full plan type and you have purchased from Currys a Xbox in a pay monthly plan type.

You will receive at your postcode RG132DN a reminder of your purchases and information about how to care for your products."

I got most of the flow right, it creates the text and creates a word document with the text, the problem is that it is trying to create one file for each row, and when you look at the files created if only shows one product even if the customer has 2 or more, so it's not iterating and adding all the products in the list.

My flow currently looks like this i tried multiple alternatives with apply to each in different sections etc but not being able to get it working any support much appreciated:

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

rzaneti 4,241 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

If I understood correctly, you would like to have the data from two different Excel records into a same Word file, grouping the data by Customer ID. If yes, you can refer to the approach described in this blog, making the necessary adjustments to fit to your use case.

Based on the blog's approach, you will first need to have a variable of type array to store the unique Customer IDs. You can then populating it with an Apply to each combined with a Condition to check if the Customer ID already exists or not in the variable.

Next you will need to loop this array variable, filtering the results from the Excel by the current iteration Customer ID. Assuming that you always have only 2 records per customer and individual inputs in your Word template, you can then populate it with expressions that reference the properties from the first array item and from the second array item, e.g. for Mr Tupert, we would have body(Filter_array)[0]['ProductType'] equals to Playstation and body(Filter_array)[1]['ProductType'] equals to Xbox.

I know the instructions above may sound a little confusing, so if you have any questions or need further clarification after checking the blog's steps, let me know :)

Let me know if it works for you or if you need any additional help!

If this is the answer to your question, please mark the post as Accepted Answer.
If this answer helps you in any way, please give it a like.

Check more Power Platform content on my website.
Lets connect on LinkedIn.

Was this reply helpful? Yes No

Chriddle

8,441 Super User 2025 Season 2 on at

Like (0)

Report

One possible approach:

Compose (Your data from Excel)

[
    {
        "Customer ID": "388023432",
        "Post Code": "RG132DN",
        "Account Name": "Tupert",
        "First Name": "Tupert",
        "Surname": "Tupertine",
        "Provider": "Currys",
        "Product Type": "Playstation",
        "Plan Type": "Pay Full",
        "Pol Number": "WP2323dad"
    },
    {
        "Customer ID": "388023432",
        "Post Code": "RG132DN",
        "Account Name": "Tupert",
        "First Name": "Tupert",
        "Surname": "Tupertine",
        "Provider": "Currys",
        "Product Type": "Xbox",
        "Plan Type": "Pay Monthly",
        "Pol Number": "Wwdw222323"
    },
    {
        "Customer ID": "238805741",
        "Post Code": "RG991HP",
        "Account Name": "Carlos",
        "First Name": "Adrian",
        "Surname": "Grace",
        "Provider": "Amazon",
        "Product Type": "Playstation",
        "Plan Type": "Pay Monthly",
        "Pol Number": "3e23e332e"
    },
    {
        "Customer ID": "638805610",
        "Post Code": "SP202NP",
        "Account Name": "Joseph",
        "First Name": "Martin",
        "Surname": "Joseph",
        "Provider": "Argos",
        "Product Type": "Xbox",
        "Plan Type": "Pay Monthly",
        "Pol Number": "qasw2222qw"
    },
    {
        "Customer ID": "638805610",
        "Post Code": "SP202NP",
        "Account Name": "Joseph",
        "First Name": "Martin",
        "Surname": "Joseph",
        "Provider": "Amazon",
        "Product Type": "Playstation",
        "Plan Type": "Pay Full",
        "Pol Number": "wd3q45523"
    },
    {
        "Customer ID": "338805910",
        "Post Code": "LN121SS",
        "Account Name": "Anthony",
        "First Name": "Claudius",
        "Surname": "Anthony",
        "Provider": "Currys",
        "Product Type": "Nintendo Switch",
        "Plan Type": "Pay Monthly",
        "Pol Number": "656yhdf"
    },
    {
        "Customer ID": "338805910",
        "Post Code": "LN121SS",
        "Account Name": "Anthony",
        "First Name": "Claudius",
        "Surname": "Anthony",
        "Provider": "Currys",
        "Product Type": "Xbox",
        "Plan Type": "Pay Full",
        "Pol Number": "5t4f4f"
    }
]

Select (restructure data)

From:

union(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		'//Customer_x0020_ID/text()'

	),
	json('[]')
)

Surname:

first(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		concat('//Item[Customer_x0020_ID="', item(), '"]/Surname/text()')

	)
)

ProiderProductPlan:

chunk(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		concat('//Item[Customer_x0020_ID="', item(), '"]/Provider/text() | //Item[Customer_x0020_ID="', item(), '"]/Product_x0020_Type/text() | //Item[Customer_x0020_ID="', item(), '"]/Plan_x0020_Type/text()')

	),
	3
)

Post Code:

first(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'), '}}'))),
		concat('//Item[Customer_x0020_ID="', item(), '"]/Post_x0020_Code/text()')

	)
)

Apply to each (customer)

body('Select')

Select 2 (text for each product)

From:

items('Apply_to_each')['ProviderProductPlan']

Map:

concat('from ',item()[0],' a ',item()[1],' in a ',item()[2],' plan type')

Compose 2 (text for each customer)

items('Apply_to_each')['Surname']

join(body('Select_2'), ', ')

items('Apply_to_each')['Post Code']

Compose 3 (text array)

outputs('Compose_2')

Result

[
  "Dear Mr Tupertine,\nYou prchased from Currys a Playstation in a Pay Full plan type, from Currys a Xbox in a Pay Monthly plan type.\nYou will receive at your postcode RG132DN a reminder of your purchases and information about how to care for your products.",
  "Dear Mr Grace,\nYou prchased from Amazon a Playstation in a Pay Monthly plan type.\nYou will receive at your postcode RG991HP a reminder of your purchases and information about how to care for your products.",
  "Dear Mr Joseph,\nYou prchased from Argos a Xbox in a Pay Monthly plan type, from Amazon a Playstation in a Pay Full plan type.\nYou will receive at your postcode SP202NP a reminder of your purchases and information about how to care for your products.",
  "Dear Mr Anthony,\nYou prchased from Currys a Nintendo Switch in a Pay Monthly plan type, from Currys a Xbox in a Pay Full plan type.\nYou will receive at your postcode LN121SS a reminder of your purchases and information about how to care for your products."
]