Data Format Error

[ashicool_10061]

Hi There,

I am trying to do data transfromation in Power Query where i am merging my table using Merge as a new . I am using dataflows as my input source in which i have imported 7 tables and i am doing Merge Query to create new table.

I developed two new query using Merge Query and when i did Merge with one of my table "Table 1" with Table 2 i am getting below error when i expanded the column.

Please see below sample of my data :

Data Source- DataFlows

Can anyone help me in resloving this error. ?

Thanks,

Ash

@amitchandak @Ashish_Mathur 

[v-heq-msft]

Hi @ashicool_10061 ,

According to your description, the error message “DataFormatError: There were more columns in the result than expected” is usually caused by a mismatch between the merged table structure and the expected format. First of all, you need to make sure that the column names and order in both tables match exactly. And verify that the data types of the columns to be merged are compatible. Because your data source is derived from a data stream, you also need to make sure that the structure (column names and data types) of each table imported in DataFlows matches the structure you expect in Power Query. You also need to have the data in DataFlows successfully refreshed and up-to-date for all tables.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ashicool_10061]

Hi @v-heq-msft ,

You mentioned all the column name and order in both table shoudl be similar however i am trying to do Merge as new querty with Left Outer join with Table 1 on Table 2 where i am keeping all the columns from Table 1 . As per my kunderstanding  in Append Query we should have Same column name and order will be same.
Secondly i have verified data format and my dataflow is being up to date and still i am facing this issue.

So i have changed my dataflow pervioulsy i was using different Dataflow-Dev and not i changed my DATAFLOW- test  which have exact same column and table names .

Any other suggestion if you have?

Thanks,

Ash

[Omid_Motamedise]

Can you share your M code here?

[ashicool_10061]

Hi @Omid_Motamedise 

Please see below :

[mussaenda]

Hi @ashicool_10061 ,

it is because of the fields with orange underline. 

Those are not present in the section inside the green section. Review it, remove them or add them in the green section and it will be solved.

[ashicool_10061]

Hi @mussaenda 

I tried the way you mentioned but still it not working. So i tried re creating the report from scratch and now as soon as i am loading the data into the report i am getting below error:

Can you help me in this 

Thanks,

Ash

[mussaenda]

Hi @ashicool_10061 , Please share the M Query code of your report from scratch

[wini_R]

Hi @ashicool_10061,

You may want to check this thread and see if this helps:
https://community.fabric.microsoft.com/t5/Service/DataFlow-Refresh-Error-and-Fix-for-added-Fields-in...