Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
NiteshS
Helper I
Helper I

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

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

1 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

View solution in original post

15 REPLIES 15
SidJay
Employee
Employee

Hi Nitesh,

 

Thank you for confirming that this resolved the issue.

miguel
Community Admin
Community Admin

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?

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.

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
Frequent Visitor

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

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
Frequent Visitor

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

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
Frequent Visitor

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"

 

Dan44_0-1701705814747.png

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. 

 

Dan44_1-1701705961761.png

Dan44_2-1701706013119.png

What do I now do? Thanks

Hi Dan44,

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

 

Thanks

Nitesh

 

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
Frequent Visitor

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?

Dan44_0-1701709464923.png

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.

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.

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 FBC Gallery Image

Fabric Monthly Update - March 2024

Check out the March 2024 Fabric update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors