Forum Discussion
Memorable MemberTables missing when connecting to dataflow from PBI desktop (2023)
It is a little buggy. Try this:
- Set all 5 tables to load. Can you see them in Power Query?
- If not, go to the tables you cannot see and add a column. Doesn't matter, just have the column be 1 or something.
- Save and let the dataflow process.
- Can you see them now? You should.
- Go back and delete those fake columns, and let it refresh again.
That is what I've done a few times this past week with dataflows that look ok in the service but cannot be seen by Power Query.
The following assumes you aren't a Microsoft employee, and you don't already know this information.
I went ahead and reported the bug, and they were aware. They seemed to acknowledge that at a least one other customer had already reported it. For me the problem happens often enough that it seemed extremely severe (eg. at least once a day my dataflow schemas would become corrupted, forcing me to re-open, edit, save).
Here are details about the CSS support case, and underlying bug. Microsoft wanted to anonymize the details for some reason, so I'm suppressing the first or last of the digits of all the identifiers. However Microsoft assures me that, by using the identifiers and their descriptions, they should be able to retrieve the bug.
Customer Support case 23030300400XXXXXIssue Definition
Schema missing for a table in a dataflow
Summary and resolution
--In power bi service, we have created a dataflow with three tables. After creating the dataflow, we have an issue while trying to view the schema of one table
--But after following the workaround in the community link by adding a column >>Save >>Remove column >>Save. You were able to see the schema of all the three tables.
-- Internal ICM chat-ticket (ICM: XXX174745 – Table schema is missing for some tables in a dataflow)
-- We got an update from the product team that this is a bug (Bug id: XXX4610 – Prevent Object Model’s “Attributes” field from being null) and fix will be deployed in the next few weeks where now it is in testing stage.
...
While Microsoft claims the bug fix will be deployed in the next few weeks, I have some doubts about the likelihood that everything will be peaches and cream. For one, the bug was pretty severe, based on my experiences, and it should have been fixed long ago (2019). Given the bug hasn't been fixed, it implies that a permanent fix may be hard to come by. Secondly I still have a "blue question mark" in the dataflow authoring experience, and it rarely goes away in a timely manner. I'm guessing that this icon indicates an underlying problem with the evaluation of dataflow entities (something that also needs to happen when saving the dataflow as well). If the authoring experience suffers from "blue question marks", then saving the dataflow is also unlikely to succeed. Its just a hunch, although CSS says the "blue question mark" is not relevant.
Hopefully the PG will surprise us, and find a way to permanently fix the bug. However I also have several other dataflow bugs, and none of the bugs ever seem to be fixed very quickly so I'm not holding my breath on this one either. My vnet-gateway bugs have been biting for a couple of years.
Based on the interactions with CSS, Microsoft agreed with the proposed workaround (add a column, save, remove a column, save). They also encourage customers to re-open this ticket if the same problem persists after a month or so.
Memorable Memberedhans You may be on to something. It seems that I was able to use your trick and correct the schema.
It just bothers me that the service doesn't alert us about the bad schema right away. Moreover, when running refresh operations on these tables, they happily run a bunch of PQ and then throw away the results - this makes no sense!
... I think I assumed that the PQ code which defines the tables would be always be fully respected. This doesn't seem to be the case; it appears that there is some additional schema being kept somewhere else in these dataflows, and the additional schema actually "trumps" the PQ code. However the additional schema isn't always maintained very carefully as you point out. (I'm not sure why I spend all that time waiting on the "validating" of my dataflow, if not for the purpose of storing the schema at the end of it all.)
FYI, I went back and looked at the json of the dataflow, as it existed before using your trick. As you can see below, the offending tables had no columns. The good tables have columns listed in the json.
Thanks for the help. You should write a blog about this or something. Otherwise I'll try to get Microsoft to add it in their "troubleshooting" documentation (or whatever). I miss the good old days when Microsoft actually maintained a KB containing the known software bugs and potential workarounds. Nowadays you are lucky if they acknowledge a bug at all, or add it to their so-called "troubleshooting" docs.
Community ChampionGlad it worked. I marked my answer as the solution so others can find it. I could write a blog, but part of me hates to as this is a bug, but I am sure others will find issues too, so maybe I will.
In any event, glad your issue was fixed.