web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / For Loop through Power...
Power Automate
Unanswered

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

(1) ShareShare
ReportReport
Posted on by 2

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:
I have the same question (0)
  • Chriddle Profile Picture
    8,416 Super User 2025 Season 2 on at
    Re: For Loop through Power BI and save it in a Excel table

    Chriddle_3-1719936667180.png

    Chriddle_4-1719936687884.png

     

     

     

     

    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

     

    body('TmpResultPlus')

     

     

    Create HTML table

    (Just to show the result)

     

    variables('Result')

     

    Chriddle_5-1719936739329.png

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 462 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 456 Moderator

#3
abm abm Profile Picture

abm abm 243 Most Valuable Professional

Last 30 days Overall leaderboard