Power Automate - Building Flows

Stuck getting a json to excel

Like (2) Share

Report

Posted on 14 Apr 2025 21:51:06 by fiddlerx

103

Hi,

So we have an on-prem Exchange Server 2019 and right now we have no ide when we can get to Exchange Online. (not important right now for this)

I want to create a Power BI dashboard that reads data from a table within an excel sheet hosted in SharePoint Online (no Ondrive yet)

Not sure how to get this fully automated since flow cannot read from on-premises mailboxes right?

My idea:

1) Use power shell to dump a json file and email it to my mailbox

2) manually put the json file to a sharepoint online folder

3) every hour OR when a new json file is uploaded (shall only read the latest file since it contains dates so I cen get a line chart in Power BI)

4) do some magic on the json file and extract that into different tables in the excel sheet mapping the sections below in the json file

striped-down-version so you get the point.

{

"mailboxcounts": {

 "date": "2025-04-14",
 "mailboxes": "5300"

},
mailboxsizes": {
  "date": 2025-04-14",
  "Above 50GB": 77,
  "Between 25-50GB", 23,

}
}

Is there any other good suggestion to get this fully automated?

How can I trigger a flow to look at the latest json file and use that to extract the information needed and then start the rest of the flow?

sorry but im lost...

any help appreciated!

Thanks!

Categories:

Business Process Flows

All responses (3)

Answers (0)

fiddlerx 103 on 23 Apr 2025 at 13:38:25

Like (0)

Report

Stuck getting a json to excel

Anyone?

It works up to the parse JSON (I can see the content in the Parse JSON output)
How can i now add the JSON sections into excel tables?
fiddlerx 103 on 15 Apr 2025 at 20:29:55

Like (0)

Report

Stuck getting a json to excel

Hi, that seems to work up-til that point. I can now see the json file content but how do I map that to my excel table? what would be the primary key etc?
Suggested answer

abm abm 32,464 Most Valuable Professional on 14 Apr 2025 at 22:26:39

Like (1)

Report

Stuck getting a json to excel

Hi

Follow the below steps

Based on the JSON file Parse JSON Schema is as follows:

{

    "type": "object",

    "properties": {

        "mailboxcounts": {

            "type": "object",

            "properties": {

                "date": {

                    "type": "string"

                },

                "mailboxes": {

                    "type": "string"

                }

            }

        },

        "mailboxsizes": {

            "type": "object",

            "properties": {

                "date": {

                    "type": "string"

                },

                "Above50GB": {

                    "type": "integer"

                },

                "Between25-50GB": {

                    "type": "integer"

                }

            }

        }

    }

}

This way you can extract the latest json file and access the values from the json content.

Let me know how it goes.

Thanks

Community site session details

Stuck getting a json to excel

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates

Community site session details

Stuck getting a json to excel

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates