cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeffN
Frequent Visitor

Get Power BI Dataflow csv from Azure Blob Storage

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 

1 ACCEPTED SOLUTION

Accepted Solutions

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:

  1. Trigger: 'When a blob is added or modified (properties only) (V2).
  2. 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.
  3. Action: Compose1 = last(outputs('Lists_blobs_(V2)')?['body/value'])
  4. Action:  Compose2 = output(Compose1)['Path'] to return the path for next step:
  5. Action: 'Get blob content using path (V2)' 
  6. 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!!

View solution in original post

3 REPLIES 3
JeffN
Frequent Visitor

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=="
  }
]
v-xiaochen-msft
Community Support
Community Support

Hi @JeffN ,

 

I did a test for you.

vxiaochenmsft_0-1641452720067.png

[
{"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"}
]

vxiaochenmsft_1-1641452733898.png

vxiaochenmsft_2-1641452745684.png

{"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'))}}

 

vxiaochenmsft_3-1641452770616.png

vxiaochenmsft_4-1641452786246.png

vxiaochenmsft_5-1641452796187.png

vxiaochenmsft_6-1641452814465.png

items('Apply_to_each_2')['ticks']
item()['ticks']
max(variables('var3'))
body('Filter_array')[0]['blob']
 
vxiaochenmsft_7-1641452884910.png

 

Best Regards,

Wearsky

 

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:

  1. Trigger: 'When a blob is added or modified (properties only) (V2).
  2. 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.
  3. Action: Compose1 = last(outputs('Lists_blobs_(V2)')?['body/value'])
  4. Action:  Compose2 = output(Compose1)['Path'] to return the path for next step:
  5. Action: 'Get blob content using path (V2)' 
  6. 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!!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Users online (1,839)