Power Platform Community Forum Thread Details

For Loop through Power BI and save it in a Excel table

Hello guys,

have the following problem. My goal is to create a pivot-like table in Excel from a Power BI table. My entries in Power BI are as follows:

Date, Quarter, Product, Price

01.01.2024, Q1, Product1, 100$
01.01.2024, Q1, Product2, 400$
01.01.2024, Q1, Product3, 60$
01.01.2024, Q1, Product4, 10$
01.01.2024, Q4, Product1, 120$
01.01.2024, Q4, Product2, 150$

At the end of the flow, the result should look like this:

Date, Product1Q1, Product2Q1, Product3Q1, Product4Q1, Product1Q4, Product2Q4

01.01.2024, 100$, 400$, 60$, 10$, 120$, 150$

It is clear that one timestamps have multiple rows. I would like to compress this into a single row.

My idea was to define a variable for each column, iterate through the rows of the Power BI table, assign the respective values to the variables based on a condition, store them in an array, and then insert them into the table using "Add a row into a table." However, I am missing the logic on how to fill the array and then insert it into the table. Could someone help me with this? Thank you in advance for the help.

Categories:

Business Process Flows

Data (Compose)

[
	{"Date": "2024-01-01", "Quarter": "Q1", "Product": "Product1", "Price": "100$"},
	{"Date": "2024-01-01", "Quarter": "Q1", "Product": "Product2", "Price": "400$"},
	{"Date": "2024-01-01", "Quarter": "Q1", "Product": "Product3", "Price": "60$"},
	{"Date": "2024-01-01", "Quarter": "Q1", "Product": "Product4", "Price": "10$"},
	{"Date": "2024-01-01", "Quarter": "Q4", "Product": "Product1", "Price": "120$"},
	{"Date": "2024-01-01", "Quarter": "Q4", "Product": "Product2", "Price": "150$"}
]

DataXml (Compose)

xml(
	json(
		concat('{"Root":{"Item":',outputs('Data'),'}}')
	)
)

Properties (Select)

From

union(
	chunk(
		xpath(
			outputs('DataXml'),
			'//Item/Product/text() | //Item/Quarter/text()'
		),
		2
	),
	json('[]')
)

Map Name

concat(first(item()), last(item()))

Map Quarter

first(item())

Map Product

last(item())

TmpResult (Select)

From

union(
	xpath(
		outputs('DataXml'),
		'//Item/Date/text()'
	),
	json('[]')
)

Map Date

item()

Initialize variable Result

Name: Result

Type: Array

Value

body('TmpResult')

EachProperty (Apply to each)

body('Properties')

TmpResultPlus (Select)

From

variables('Result')

Map

addProperty(
	item(),
	items('EachProperty')['Name'],
	xpath(
		outputs('DataXml'),
		concat('string(//Item[Date="',item()['Date'],'" and Product="',items('EachProperty')['Product'],'" and Quarter="',items('EachProperty')['Quarter'],'"]/Price)')
	)
)

Set variable Result

Name: Result

Value