Tables missing when connecting to dataflow from PBI desktop (2023)

[dbeavon3]

There were some similar posts about this in 2019,  and follow-up claims about a Microsoft bug fix.

I think this may be a new bug or a regression.  I'm fairly new to dataflows so I can't say for certain.

I have five tables in a PBI dataflow where "enable load" is set.  Some tables are "computed entities" and others are not.  I'm only able to see three tables after I refresh the dataflow in the service, and try to connect to it from the PBI desktop.  The three that I'm seeing are related - they are a set that incudes two computed entities and one source table.  Unfortunately I'm not seeing the final two remaining tables, when connecting from the PBI desktop.  They are standard tables, rather than computed entities.

When reviewing the download status for the dataflow (available from refresh history) I see that the two missing tables were processed, and no error was generated.  I have valid data in the related status columns: 

Start time, End time, Duration, Bytes processed (KB)

Here is one of the most interesting part.  If I uncheck "enable load" on the three tables that are already visible, then the other two tables will finally be visible to the desktop!  It seems pretty silly.   In other words, those two tables aren't a problem by themselves.  The only time I have problems is if I refresh the dataflow with all five tables in it at the same time. 

I really am not having fun with these dataflows.  I'll probably open yet another bug ticket with CSS.  Just hoping someone may already know what is going on here.  I don't think it is related to dates (I think that is the thing that most people were blaming for the past few years).  BTW, does anyone understand why these Power BI bugs seem to be biting folks for multiple years at a time?  Everyone says the product is being improved very quickly, yet somehow the well-known bugs are never fixed.

[edhans · Accepted solution]

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.

[Apotygma_BE]

To add to the topic : 

• Tried adding a dummy column and refreshed : same issue still occuring
• Found another solution : Issue could be linked to stale connection or Privacy options. I'm trying to switch everything to public and Ignoring privacy options. 

[edhans · Accepted solution]

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.

[nickske]

Thanks for the information!  I had the same problem in 3 dataflows.   Yesterday, I added columns and the problems was solved.  

[dbeavon3]

@edhans Thanks for the reply.

Can you share the underlying principles or theories behind your suggestion?  I'd rather not flail around without first having some sort of framework for understanding why the dataflows aren't working.  (I already waste tremendous amounts of time with Power BI tools, even when things are working as expected.)  Also can you tell me, if you ever open your own support cases with CSS?  It doesn't seem right to me that some people do the work of reporting bugs to CSS and others don't do their share of that work.  Even if bugs aren't fixed, Microsoft should have an extensive KB list that describes the bugs that are being reported by their customers.  </rant>

There is a clue that something is wrong in the service as well.  The dataflow's summary screen (before you go into the authoring experience) shows no columns for the tables that are missing.  If I hit the little drop-down expansion button, I'm not seeing the columns that should be there.

Here is another thing I should mention.  The problem goes away if I decrease the size of data in one of my tables.  One of the tables is extremely large, but it consists of only one row.  It has a base64-text-encoded binary value.

What I really wished, when I first started using dataflows, is that Microsoft wouldn't have put such a heavy reliance on CSV for encoding data.  It is no surprise that this stuff is unreliable.  I think there are lots of better options these days, and I don't think anyone with a basic degree in computer science would encode their mission-critical information in CSV tables.  (Please don't suggest I try using datamarts.  I REALLY don't want to go down that rabbit hole as well.  I just want them to give me the option of several file types, like json, xml, avro, parquet, sqlite, or whatever other file makes sense for a given dataset).

Also I wish Microsoft would expose the unexpected/internal errors that their software is producing.  My guess is that there are lots of things going wrong internally.  However they don't seem to want to display error messages to users, and they don't have any logging mechanisms either.

 Thanks, again for your pointers.  I'll probably start the support case today.  But usually the process takes 1 or 2 months before I reach the engineers who actually have access to read their code.

[edhans]

I've seen issues where it doesn't look like the schema of the table is being published for consumption correctly. "Schema" might not be the right word. By changing the tables, saving and refreshing, then changing back, it forces the schema to be updated and republished.

[dbeavon3]

@edhans 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.

[edhans]

Glad 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.

[dbeavon3]

@edhans 

I just had a call with folks in CSS.  I think there is awareness of these problems, but of course they won't tell me how many prior cases had been opened or anything like that.  And I suspect that this isn't one of the highest priority bugs to be fixed in the PBI dataflows.

Dataflows are quite challenging technology to use effectively. And if a user gets as far as finding this corrupted-schema issue, they are probably doing pretty well, relatively speaking.

Thanks again.

[edhans]

Excellent. Thanks for letting me know. Glad it is on their radar.

[dbeavon3 · Accepted solution]

@edhans 

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 23030300400XXXXX

Issue 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.

[priyank07]

**at least once a day my dataflow schemas would become corrupted, forcing me to re-open, edit, save**
I opened the edit table, added a new dummy column, removed it and saved the table. That way the table schema reappeared. Thanks!