topic Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2 in Dataflow

Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

NiteshS — Fri, 21 Jul 2023 13:04:05 GMT

Dear Community,

I imported tables from Dataverse using Dataflow Gen2 in my Lakehouse tables. In the power query editor screen, all the columns were visible. But when the table was published, few of the columns do not appear in the Lakehouse Tables.

Canyou please suggest me where I am going wrong or if this is due to the Preview feature.

Thanks

Nitesh

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

miguel — Tue, 25 Jul 2023 05:09:33 GMT

Could you confirm if these columns are available in the Lakehouse and the issue is simply visualizing them on Dataflows Gen2? or is the issue that the columns do not exist in the lakehouse despite you defining those columns to be created by your original dataflow gen2 with the lakehouse as the output destination?

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

SidJay — Tue, 25 Jul 2023 06:06:25 GMT

Hi Nitesh,

Could you please also confirm that you have re-edited the mapping (in case you added columns after the initial mapping)? We do not yet have a mode where the mapping is automatically updated everytime you add a column. You have to explictly edit the Output Destinations settings.

Thanks

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

NiteshS — Tue, 25 Jul 2023 12:42:17 GMT

Thank you for the response. This numeric column were not getting published when the destination was not selected before publishing. Now after selecting the destination, I am able to see the numeric columns.

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

NiteshS — Tue, 25 Jul 2023 12:43:44 GMT

Thank you for the response SidJay I am able to see the numeric columns when I select the destination before publishing. I had not selected the destination earlier before publishing.

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

SidJay — Tue, 25 Jul 2023 17:40:01 GMT

Hi Nitesh,

Thank you for confirming that this resolved the issue.

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

NiteshS — Wed, 26 Jul 2023 03:48:25 GMT

I used Dataflow Gen 2 to get my Tables from Dataverse. I did not add any new columns and did not change the mapping as well because the Type was fine for all the columns.

I think just choosing the destination folder solved this issue.

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

Dan44 — Thu, 30 Nov 2023 10:15:12 GMT

SidJay unfortunately that doesn't work. When I add a new column, despite entering the Data Destination settings within my dataflow and selecting "Refresh destination schema" and/or "Reset column mappings", my new column is not to be found. Only the current columns from the already published table in the lakehouse are showing. Funny enough I can see my column in the dropdown in Source but of course, if I change this to something which contridicts its current destination the dataflow will fail.

Like someone else commented, I am having to manually delete the table in the lakehouse and republish it - along with all of the configuration that goes into my dataset. It is very frustrating, especially since Microsoft has disabled the ability to refresh data for published Excel reports in the workspace, where the report is built through Power Pivot.

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

miguel — Thu, 30 Nov 2023 18:29:59 GMT

Hi!

I believe the scenario that you're describing is a bit different from the one mentioned in this topic.

The scenario that you're describing sounds more of a dynamic query that can output more columns than when the first data destination was setup.

However, the scenario from this thread is around how the first time a data destination was set it had all columns, but some of them were not showing in the data destination after the refresh succeded for the first time.

Given that your scenario described is different, please go ahead and create a new topic / thread in this forum so we can have a better understanding of it.

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

Dan44 — Fri, 01 Dec 2023 11:21:37 GMT

Hi miguel thank you for your reply.

I'm not sure what you mean by a dynamic query. My issue boils down to I have a dataflow gen2 with the output destination to a lakehouse, and then if I insert ANY new column, I cannot add this to my current destination table. The only way to include this new column is to proceed to delete the current table in the lakehouse and republish. This appears because since the current table doesn't already have this new column present as a destination, it simply limits adding it (i.e. although I can see the new column in the dropdown for Source within the Data destination settings, there is no place to configure it to in Current destination).

Is this however expected behaviour?

Regards

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

miguel — Mon, 04 Dec 2023 15:33:54 GMT

I'm not sure I follow. If a table exists in the lakehouse, you can go into the Lkaehouse to alter the table and modify the schema as you wish. Then you'll be able to come back to the dataflow and re-map the data destination configuration to that table.

Dataflow doesn't have the capability at the moment to issue an ALTER command to modify the schema of your table either by renaming, removing or adding new columns, but the process described above is what I'd suggest to move forward with. Dataflow expects a fixed schema to be defined at the moment, but you can request a feature that can ALTER the destination table based on some logics that you wish to set (by either renaming the columns, adding new columns or dropping some specific columns on a refresh) on the ideas portal via the link below:

https://aka.ms/FabricIdeas

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

Dan44 — Mon, 04 Dec 2023 16:12:19 GMT

I had no idea you could use an ALTER command. Can you please walk me through with this example?

I have one column in my table named Test which is "Custom1"

I now have returned to my dataflow to insert a new column: "Custom2". As seen, it is there in my power query but I cannot publish it to my current Test tablem through destination settings, because there is no destination to use - despite clicking refresh destination schema and reset column mappings.

What do I now do? Thanks

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

miguel — Mon, 04 Dec 2023 16:17:54 GMT

How to modify an existing table from a lakehouse or a warehouse would be a question better suited for the forum on Lakehouse / Warehouse from the link below:

https://aka.ms/SYN-Community

I'd highly encourage asking the question on how to update your table schema on your Lakehouse / Warehouse in that forum.

The dataflow simply uses the information of the table that it finds available from your lakehouse / warehouse, but it doesn't modify the schema of it. It can create a new table entirely with some specific schema on the initial load, but that's only for new tables.

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

Dan44 — Mon, 04 Dec 2023 17:07:42 GMT

Thanks I will look into this. However, I was under the impression that altering tables with T-SQL is something only possible in the warehouse?

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

miguel — Mon, 04 Dec 2023 17:17:22 GMT

altering a table is out of the scope of what a Dataflow Gen2 does today. For specific questions about Lakehouse it would be best to post them in the other forum.

However, the concept remains the same from the Dataflow Gen2 side. You can use a SQL Server as a destination, alter the schema of the table and then update your data destination settings in the dataflow itself. The Dataflow Gen2, at the moment, does not have the ability to change / alter the schema of an existing table from any sources.

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

NiteshS — Thu, 14 Dec 2023 09:51:04 GMT

Hi Dan44,

You need to import the table again to get the new columns. This appraoch worked for.

Thanks

Nitesh

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

Tanayraj — Mon, 06 May 2024 14:45:19 GMT

Hey Dan44 , I am facing the same kind of issues. Any solution?

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

Tanayraj — Mon, 06 May 2024 14:45:21 GMT

Hey Dan44 , I am facing the same kind of issues. Any solution?

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

Dan44 — Mon, 06 May 2024 20:48:51 GMT

Hi Tanayraj

This is no longer an issue since Microsoft now allow you to publish the tables through dynamic schema as you can see below, at least for a lakehouse

Re: Few columns are missing from Lakehouse tables when fetched via Dataflow Gen2

Tanayraj — Tue, 07 May 2024 07:28:34 GMT

You are right Dan44 But I am trying to add a new column in the warehouse schema. In my Dataflow power query, I can see a new column that I want to add but when I select the destination table (Which already exists in a warehouse), I am not able to see the new column.