External Tool to Update Dataflow Metadata??

[vckbx]

We're using dataflows pretty heavily. Here's a situation we run into frequently:

Add a new column to an upstream dataflow.

Have several downstream dataflows.

We have to refresh upstream dataflow, then open each downstream data flow and save in order to add the new field.

Is there currently any way to add a new field metadata to a dataflow without opening it in PBI service?

[edhans]

No. There is no XMLA endpoint for accessing dataflows at this stage.  The REST API for dataflows let you manage many aspects of a dataflow, but not editing the M code. Dataflows - REST API (Power BI Power BI REST APIs) | Microsoft Learn

[vckbx]

Do you know if this is on the development roadmap?

[edhans · Accepted solution]

No. I don't even see an idea for it though. What are you trying to accomplish? Even with an external tool, you'd have to edit the M code, save it, then initiate a refresh. What will the external tool provide that whe web UI isn't? Are you just trying to script a bunch of changes?

[vckbx]

I want to be able to edit the M code for all downstream dataflows, then refresh the upstream dataflow and have the data populate in the downstream dataflows. Currently, I have to:

1) Add the new field to the upstream dataflow

2) Refresh it

3) Wait for it to finish

4) Open the first downstream dataflow

5) Save it

6) Refresh it

7) Wait for refresh to finish

.... etc etc for each downstream dataflow

[edhans]

You can speed this up by editing the first dataflow and tell it to only keep the top 100 records. It should refresh in seconds. Then add that column to all downstream dataflows. Then remove that Keep top 100 records from the first dataflow and let all of them fully refresh.

An XMLA endpoint to edit won't fix that. You cannot add columns to downstream dataflows that don't exist yet in upstream. 

[vckbx]

That workaround may save me some time, thanks.

As for "You cannot add columns to downstream dataflows that don't exist yet in upstream"... Maybe I'm not understanding fully, but I would edit the upstream dataflow metadata, then the first downstream metadata, then the next, so the fields would exist in the upstream dataflow, it just wouldn't contain any data.

[edhans]

You can do that today. When you save an upstream dataflow and save it, it creates the schema. Downstream dataflows or Power BI can see those columns but the table is empty during the refresh. If you don't need any data to do this, then just do that.

However, if you edit DF1 this way, then edit DF2 to include columns from 1, save and refresh, then start to work on DF3, if DF1 is still refreshing and DF2 starts a refresh, it may fail, or cause an issue in DF1. 

Safer bet is to do the First 100 rows or something so you can at least see a bit of data to play with and know the refreshes are finishing quickly.