Power BI adding steps after Save&Close

[crodrojas]

Hello Everyone,
I'm having issues with a dataflow Im trying to setup and not sure why this is happening.
All Im doing is connectig thorugh sharepoint folder connector, doing some filtering to get to the files location. I added a column to display the content of the CSV files and another column to retrieve the wk number from the files name. All works flawlessly in PBI Desktop, All good also in My workpace dataflow until I hit Save& Close. It will add a step to change the column type to the added column (csv content) then the error that cannot convert the "value of type Table to type text", making all other transformation failed after that.. Please see the attache image of both the desktop and the dataflow version.. 

Any help will be much appreciated.. Thx

DataflowPBI Desktop

[v-jingzhang · Accepted solution]

Hi @crodrojas 

I'm afraid the Table data type is not supported in Power BI dataflows. You could refer to this doc: https://docs.microsoft.com/power-bi/transform-model/dataflows/dataflows-features-limitations#:~:text... 

Based on my test, when I tried to save and close a dataflow which has a table column in a query, I could save it successfully. But when I opened it to check the steps again, I found it automatically adds the following step to remove the table column before saving. Apparently columns of unsupported data types (table, record, list, binary, function) are removed automatically. 

Table.RemoveColumns(#"Merged queries", Table.ColumnsOfType(#"Merged queries", {type table, type record, type list, type nullable binary, type binary, type function}))

I suggest that you expand the table column to bring all table data into the table before applying. In Power BI Desktop, although you can close and apply it successfully, you will find this table column doesn't exist in the data model. You can check the Data view after applying. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[crodrojas]

Good Day Vijay, The options explained are for PBI Desktop, but I found the same under Home Tab>options>Project or Global within the power query of the Dataflow Im trying to create. Unfortunately that doesn't work even though I untick the option of Automatically detect column type. Still adding the additional steps and also I can see a warning   "The query "Name" contain untype columns",

I went a bit further and changed the formula of the added step from text to any  and PBI still add the steps to swith it back to text.

[Vijay_A_Verma]

File - Options & settings - Options - Current File - Data load - Uncheck the below

(if you do this under Global, then changes will be applicable for all files. If you do it for Current file, then change is applicable only for Current file)