Unanswered

Get Power BI Dataflow csv from Azure Blob Storage

(0) Share

Report

Posted on by JeffN

Greetings experts! I'm trying to retrieve Power BI Dataflow connected to Gen2 Blob storage location. I can retrieve the list of blobs (Lists blob (V2)) that represent the entities from each Dataflow refresh from the 'model.json.snapshots/ folder. Now I need to select the name of the blob with the most recent data in it, e.g. the third blob in this set:

model.json@snapshot=2021-12-29T13:43:11.8221557Z
model.json@snapshot=2021-12-29T15:42:47.6820754Z
model.json@snapshot=2022-01-02T13:57:44.4572616Z

Then I plan to set that file name to a variable and use as a parameter in a Get Blob Content action.

Being new to Power Automate and finding on-line resources to be very inconsistent, I'm looking for some expert help.

Kind regards,
Jeff

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

JeffN 31 on at

Like (0)

Report

I should also note that the output from the Lists Blob V2 action is a JSON object, which I then pass to a Create CSV table that generates the lines pasted above. I need to get the JSON member for the most recent datetime, so maybe it is not necessary to make the csv first. How can I extract/query the member with the recent datetime? Here is the JSON output from the List blobs action:
[
  {
    "Id": "xxxxx==",
    "Name": "model.json@snapshot=2021-12-29T13:43:11.8221557Z",
    "DisplayName": "model.json@snapshot=2021-12-29T13:43:11.8221557Z",
    "Path": "/powerbi/Azure test/Excel Datasource via Gateway/model.json.snapshots/model.json@snapshot=2021-12-29T13:43:11.8221557Z",
    "LastModified": "2021-12-29T13:43:11Z",
    "Size": 1267,
    "MediaType": "application/octet-stream",
    "IsFolder": false,
    "ETag": "\"xxxxx\"",
    "FileLocator": "xxxxx=="
  },
  {
    "Id": "xxxxx==",
    "Name": "model.json@snapshot=2021-12-29T15:42:47.6820754Z",
    "DisplayName": "model.json@snapshot=2021-12-29T15:42:47.6820754Z",
    "Path": "/powerbi/Azure test/Excel Datasource via Gateway/model.json.snapshots/model.json@snapshot=2021-12-29T15:42:47.6820754Z",
    "LastModified": "2021-12-29T15:42:47Z",
    "Size": 1587,
    "MediaType": "application/octet-stream",
    "IsFolder": false,
    "ETag": "\"xxxxx\"",
    "FileLocator": "xxxxx=="
  },
  {
    "Id": "xxxxx==",
    "Name": "model.json@snapshot=2022-01-02T13:57:44.4572616Z",
    "DisplayName": "model.json@snapshot=2022-01-02T13:57:44.4572616Z",
    "Path": "/powerbi/Azure test/Excel Datasource via Gateway/model.json.snapshots/model.json@snapshot=2022-01-02T13:57:44.4572616Z",
    "LastModified": "2022-01-02T13:57:44Z",
    "Size": 1587,
    "MediaType": "application/octet-stream",
    "IsFolder": false,
    "ETag": "\"xxxxx\"",
    "FileLocator": "xxxxx=="
  }
]

Was this reply helpful? Yes No
v-xiaochen-msft on at

Like (1)

Report
Hi @JeffN ,

I did a test for you.

[ {"blob":"model.json@snapshot=2021-12-29T13:43:11.8221557Z"}, {"blob":"model.json@snapshot=2021-12-29T15:42:47.6820754Z"}, {"blob":"model.json@snapshot=2022-01-02T13:57:44.4572616Z"} ]

{"blob":@{items('Apply_to_each')['blob']},"Date":@{formatDateTime(substring(items('Apply_to_each')['blob'],add(1,indexOf(items('Apply_to_each')['blob'],'='))),'dd-MM-yyyyTHH:mm:ss')},"ticks":@{ticks(formatDateTime(substring(items('Apply_to_each')['blob'],add(1,indexOf(items('Apply_to_each')['blob'],'='))),'yyyy-MM-ddTHH:mm:ss'))}}

items('Apply_to_each_2')['ticks']

item()['ticks']

max(variables('var3'))

body('Filter_array')[0]['blob']

Best Regards,

Wearsky

Was this reply helpful? Yes No
Verified answer

JeffN 31 on at

Like (0)

Report
Thanks Wearsky! I could not figure out how to integrate your suggestion into my solution, but it did force me to study JSON and the Workflow Language Definition to better understand your suggestion and ultimately create a solution. Here is what I did:
Trigger: 'When a blob is added or modified (properties only) (V2).
Action: 'Lists blobs (V2)' this returns a collection from the folder in Azure Blob Storage that holds my Power BI dataflow entities in csv format.
Action: Compose1 = last(outputs('Lists_blobs_(V2)')?['body/value'])
Action: Compose2 = output(Compose1)['Path'] to return the path for next step:
Action: 'Get blob content using path (V2)'
Action: 'Create file' = Using Body from step 5
This is working as desired, although it relies on the last blob in the collection as always being the last one created (i.e. the csv Power BI entity from the most recent refresh). I suppose that there is a way to make this more robust using your method, but I haven't been able to work that out (yet).

Thanks again!!

Was this reply helpful? Yes No