Can't Append Data to Excel Doc

[Tmk123]

New to this so please go easy!

I am pulling data out of a Power BI dataset and want to append it to a table in an existing Excel doc.  This is what I have so far:

I was able to figure out how to extract the data I wanted out of Power BI.

However, using the setup I have below inputs all of the data into one cell.  I know this is wrong but I cannot figure out how to set this up so that the value for each is inserted into the right cell.  I know I put the "output" into the date field only.  That was just a test to see what happens.

It puts all this data into cell A2

FYI... I don't have any Dynamic Content to select from other than "Outputs".

Thanks!!

[rzaneti]

Hi @Tmk123 ,

It looks like you will need to access some object properties to make your flow work properly.

Could you please share the raw outputs of your 'Compose' action, so we can identify which properties must be accessed? You can find the raw outputs by clicking in the button highlighted below (but in your 'Compose' action).

If you have any trouble to access the raw outputs, you can find a step by step in this article.

[Tmk123]

Hi,

Thanks for your response.  🙂  I believe I have here what you requested. 

{

    "statusCode": 200,

    "headers": {

        "Cache-Control": "no-store, must-revalidate, no-cache",

        "Pragma": "no-cache",

        "Transfer-Encoding": "chunked",

        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",

        "X-Frame-Options": "DENY",

        "X-Content-Type-Options": "nosniff",

        "RequestId": "efcf1da7-5b10-45e5-9c49-8c4adf3a942f",

        "Access-Control-Expose-Headers": "RequestId",

        "Timing-Allow-Origin": "*",

        "x-ms-apihub-cached-response": "false",

        "x-ms-apihub-obo": "false",

        "Date": "Thu, 19 Oct 2023 19:31:33 GMT",

        "Content-Type": "application/json"

    },

    "body": {

        "results": [

            {

                "tables": [

                    {

                        "rows": [

                            {

                                "[Date]": "10/19/2023",

                                "[Marlinn Inventory Total]": 222222.63,

                                "[Marlinn Job Allocation Total]": 2222.55,

                                "[Marlinn Non-Job Allocation Total]": 222.3599999996,

                                "[Marlinn Last Sale Bucket >24]": 222

                            }

                        ]

                    }

                ]

            }

        ],

        "firstTableRows": [

            {

                "[Date]": "10/19/2023",

                "[Marlinn Inventory Total]": 222222.63,

                "[Marlinn Job Allocation Total]": 2222.55,

                "[Marlinn Non-Job Allocation Total]": 222.3599999996,

                "[Marlinn Last Sale Bucket >24]": 222

            }

        ]

    }

}

[rzaneti · Accepted solution]

Hi @Tmk123 ,

Sorry for the late response. I ran some tests with the outputs that you provided and got a possible solution. 

I've stored your exact outputs in a Compose action:

Then I used this expression in a new Compose, to store only the data pulled from your PBI (firstTableRows property):

Expression: body('Compose')['firstTableRows']

It is also possible to extract data from the 'table/rows' property. To do it, you can use this expression instead of the above one: body('Compose')['results'][0]['tables'][0]['rows']

Then I've inserted an 'Apply to each' loop, using the dynamic outputs from 'Compose 2' as input: 

Finally, inside the loop I included the 'Add row to a table' action, using as input an expression that follows the logic highlighted in yellow:

For each of your columns, you will need to use the expression item()['columnName']. Just be careful here, because you will have to include the square brackets from your column name inside the quotes of the expression: 

item()['[Date]']

Here is the output:

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

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 

[Tmk123]

Thank you so much!!  This helped me learn a little more about Power Automate and I appreciate that!

The only slight modification that I made was to the Compose step.  I received the following error (in red) but then noticed I had a "First table rows" as a Dynamic Content option and selected that (see screenshot below).  I ran it and the flow worked.  Thanks again for all of your help!!

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The 'inputs' property of template action 'Compose' at line '1' and column '1960' is not valid. The action cannot reference itself.'.'.