Rookie question: Turning column containg arrays into dates

[Aoterhals]

Hi, 

I'm a Power Automate and Power BI rookie. I'm currently looking into the Vimeo Analytics API. We can a data type, say video views, per day, week, month or year. For a yearly view, that's fine, that's just one column of data. However, I am interested in getting the daily data. The obstacle I face is that I load the data into Dataverse. If I wanted to load in views, impressions and finishes per day, I'd get 3x365=1095 columns. Dataverse only supports 1024. In addition, splitting all the data into separate columns would be a massive headache and the job would be big and sluggish to say the least. 

My thought is, what if I used Power Automate to turn the data into an array. Instead of a table with columns like 01.10.2024=24, 02.10.2024 2=42, 03.10.2024 = 26, I'd get just a column with cells containing arrays like [2024-02-01T00:00:00+00:00=24, 2024-02-02T00:00:00+00:00=42, 2024-03-01T00:00:00+00:00]. I could do this with a Select and some join actions, since the video URL is unique. 

That would only really be helpful if there was a way in Power BI to read this data and turn it into a table on the other end, though. Can anyone point me in the right direction?

Edit: I should also mention that API will only give me dates per video for which there are actual views, impressions or the like. So there may be gaps in the date chain, certainly per video, but possibly also per year. 

[Aoterhals]

This actually "just worked", it's beautiful. Turned a possible Dataverse table from over 1000 columns to just a handful and loads and transforms really quickly in PowerBI. Thanks!

[rajendraongole1 · Accepted solution]

Hi @Aoterhals - Use Power Automate to pull Vimeo data and store it as JSON arrays in Dataverse.In Power BI, use Power Query to parse and expand these JSON arrays into a table format.Handle any missing dates with a calendar table and ensure there are no data gaps.Build visualizations for reporting.

In Power BI, 

In Power Query, find the column that holds the JSON arrays (e.g., views per day).
Use the "Transform" > "JSON" > "Parse JSON" option to extract the data.
After parsing the JSON, expand the resulting records into rows. Each row will represent a day's data with the corresponding metrics (views, impressions, etc.).

Json.Document - PowerQuery M | Microsoft Learn

Hope this helps.

[Aoterhals]

Thank you, @rajendraongole1. That's a very helpful summary. I will probably have to extract and filter the data into my own array, the info is stored on several levels. In order to store that array as a JSON, can I just json() it?

Example:
Vimeo API HTTP result -> Select -> Compose "json('Outputs('Select')"

[rajendraongole1]

Hi @Aoterhals - Yes, Since Vimeo API only returns dates where metrics (views, impressions) exist, you may have gaps in your daily data.You can generate a calendar table in Power BI to have a continuous date range, and then use this calendar to join with your parsed data. This way, missing dates will show as blanks in your report.

In Power Query, find the column that holds the JSON arrays (e.g., views per day).

• Use the "Transform" > "JSON" > "Parse JSON" option to extract the data.After parsing the JSON, expand the resulting records into rows. Each row will represent a day's data with the corresponding metrics (views, impressions, etc.).

[Aoterhals]

Thank you very much, @rajendraongole1! I believe this will work, but it will be a few days until I can sit down and work through all of it. I'll come back and mark this as a solution if there's no further questions. Thanks so much!