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

[NiteshS]

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

[SidJay]

Hi Nitesh,

Thank you for confirming that this resolved the issue.

[miguel]

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?

[NiteshS]

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.

[SidJay · Accepted solution]

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

[Dan44]

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

[miguel]

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.

[Dan44]

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

[miguel]

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 

[Dan44]

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

[Tanayraj]

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

[Dan44]

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

[Tanayraj]

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. 

[NiteshS]

Try importing the table again to see any new column that you added to your source table. This is what I learnt after multiple attempt.

[Tanayraj]

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

[NiteshS]

Hi Dan44,

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

Thanks

Nitesh

[miguel]

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.

[Dan44]

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?

[miguel]

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.

[NiteshS]

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.

[NiteshS]

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.