PowerBI - run a query against a dataset to add new rows in an excel sheet

[thayward]

Hi there,

Currently, I pull data from a CRM system using an API. Because of this, we can't look at the 'state' of the system historically as it updates automatically.

My thinking is to create a DAX formula that summarises the data I need, then run this through a 'Run a query against a dataset' and then add the result from the DAX formula into a new row in an Excel spreadsheet.

I've got a DAX formula which uses 'SUMMARIZECOLUMNS' to gain the result I want, but I am having trouble putting this into an Excel spreadsheet as new rows.

Could anyone help me on this?

Thanks,

Tom

[renearide]

@thayward Did you find any solution? I also need to run a query and add rows in Excel.

[thayward · Accepted solution]

@renearide I did find a solution but it's not the most ideal one.

This is the flow I am using. Essentially it runs the query, creates a CSV table of results, then that table of results is put into a new row in Excel. The problem with this is that it puts ALL the results into one cell, so you have to do transformations on the data afterwards to extract the data. The Excel my flow creates is pulled back into PowerBI anyway so I can use some Power Query functions to transform each cell into the actual table I desire.

I hope this helps.

[Jimmy_Garita]

If anyone is reading this old post, the solution I found was to run a ForEach loop of the data returned by PowerBI (Since it's an array), add the "Add a row into a table" step and then used this expression in any of your desired fields:

items('Apply_to_each')?['<Name of the query>[<Name of column>]']

So let's say you have a query called "Reports" and the column is called "Created Date", it would look like this

items('Apply_to_each')?['Reports[Created Date]']

And that would return only that specific value instead of the whole table.

[Venson]

Thank you Sir, may please share some screen capture for this method ? I tried but not easy to understand. many thanks!

[bkar81]

I am trying to run a query against a dataset and output the result to an Excel. When it runs, it is for all the rows, but for all the rows, I am getting only NULL output and the Excel file has only blank rows (all column values are blank). I am not sure how to achieve it.

I used item()?['System Name'] , item()?['SLA Time'] for the column inputs and so on

Below is the output I am getting from "Run a query against a dataset" step

I am not sure where I am going wrong. Any help would be appreciated

[
  {
    "[System Name]": "System AAA",
    "[SLA Time]": "1899-12-30T08:00:00",
    "[Start Time for System]": "2023-06-16T07:35:00",
    "[End Time for System]": "2023-06-16T08:05:00",
    "[SLA Breach]": 1,
    "[Status for System]": "SUCCESS"
  },
  {
    "[System Name]": "System BBB",
    "[SLA Time]": "1899-12-30T08:00:00",
    "[Start Time for System]": "2023-06-16T06:30:00",
    "[End Time for System]": "2023-06-16T06:45:00",
    "[SLA Breach]": 0,
    "[Status for System]": "SUCCESS"
  },
  {
    "[System Name]": "System CCC",
    "[SLA Time]": "1899-12-30T07:00:00",
    "[Start Time for System]": "2023-06-16T06:30:00",
    "[SLA Breach]": 0,
    "[Status for System]": "IN-PROGRESS"
  }
]

[BurcuIsik]

How can I reset the Excel file every time before I fill it?

[mmicsa · Accepted solution]

Hi all

I was struggling with the same issue, and managed to finally solve it, so I'll leave my solution here.

A few introductory words about my use case first. 

I have a PowerApps application that stores data in a SharePoint list. I was looking for a way of automatically generating an Excel output based on that data, but with the added difficulty of having to also run 2 Power Queries where one result is used to filter the other result.

In the end, I added the 2 Power Queries to a Power BI and just did a basic table, just to be able to map the required data:

When this was done, well, basically I followed this video from Reza : How to Export Power BI Data to Excel | Query against a dataset (youtube.com)

Unfortunately, for what I needed, large export, not launched from the Dashboard, Reza and all others I could find, only exported to CSV, not Excel.
Still, it was a good starting point.

Now, here comes my twist to the solution, and a massive shoutout to a colleague from work who helped me with this issue. 

Instead of CSV table, we used the Parse JSON node as, well, the output of the "Run a query ... " is actually a JSON.

The "catch" here is that you have to generate your own Schema for this to work.

In order to generate the Schema, I used this webpage: Excel to JSON Converter: Convert Excel sheet to JSON table format (codebeautify.org)

But you can obviously use whatever solution you find.

Once that is done, it's just a matter of mapping you excel table rows to the output:

Now, as a last piece of advice, save yourself the pain, and check if values exist (hence all the if's).

It really doesn't like missing data.

As that was another pain point (ask me how I know), here's an example Expression of how to check if there is a value:

if(contains(item(), 'your item here'), item()['your item here'], '')

So, that's pretty much it and solves all my problems, no premium connectors, no conversions or anything, just built in functionality.

Hope this saves someone a few days of their lives (again, ask me how I know 😁)

[thayward]

Wow thank you mmicsa!! That works absolutely brilliantly. I spent some time getting round the JSON schema but managed to get ChatGPT to write the schema by giving it the tabular format of what I wanted.

Very useful. Thank you for your help.

[DeGiacomo7272]

Where did that formula "items('Apply_to_each')?['Reports[Created Date]']" go, was that the formula you entered in the apply to each? Or did you put the outputs of PBI in the apply to each and that formula you mentioned was entered in the "Add a row into table"?

[jsarlii]

Hi @mmicsa ,

thank you for your post - I am trying to replicate this to my flow (to get nr. of emails from outlook and store it to Sharepoint list) however I am stuck with the "IF" functions.

Can you please share detail of this function? I do not have item as available field when writing expression and switching to dynamic content. I have only following to select:

The only way how to get item from Parse JSON is to create nested Apply to each steps but this is returning error in flow.

Thank you for any help!
Ivanuska

[mmicsa]

Hello

You're referencing the item incorrectly.

It's item(),what part of the item.

If you think of it like an array, (cause it kinda is), you're trying to tell it what "part" of the array you want.

                    if(contains(item(), 'your item here'), item()['your item here'], '')

Translation: if(not blank("array","identifier"), then Array()[Identifier], blank)

Item() - just references the current item, as we are in a loop.

To get the rest, take one of your failed runs and get the raw output and that will help you identify the correct names.

Hope this helps.

[jsarlii]

Hi @mmicsa ,

this is the output of Parse JSON action:

{
  "Mailboxes[Folder Path]": "\\Inbox\\Completed\\",
  "Mailboxes[Mailbox]": "mail@company.com",
  "[Nr. of Emails]": 10,
  "[Upload Date]": "2024-03-26T00:00:00"
}

Could you please point me to the right direction how to refer to the object within this array?

Thank you
Ivanuska

[mmicsa]

Should be something like:

If(contains(item(),'Mailboxes[Folder Path]', item()['Mailboxes[Folder Path]'],'')

Good luck.