Power BI Gateway Logs Report - There were more columns in the result than expected.

[Belgarion42]

My organization has multiple gateway servers, and I am attempting to set up log reports for each of them using the Microsot .pbit file. For most of the servers it works fine. However, on a couple of them--including one I've been working on for the last two days--I have been getting this error:

When I check for more details in Transform Data, I see -

DataFormat.Error: There were more columns in the result than expected.
Details:
Count=17

I have tried the one suggestion I found so far, in a thread here from a few years ago. I found the gateway config file Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file in the \Program Files\On-premises data gateway and changed the parameter ReportFileCount to '17'. I'm still getting the same error.

Can someone help me figure out how to resolve this? 

[Belgarion42]

Thank you for the suggestions! It turns out the real problem isn't in DateTable. It's at the Grouped Rows step in QueryExecutionReport:

= Table.Group(#"Expanded DataSource1 - path", {"Index", "GatewayObjectId", "Source.Name", "RequestId", "QueryTrackingId", "QueryExecutionEndTimeUTC", "QueryExecutionDuration(ms)", "QueryType", "DataReadingAndSerializationDuration(ms)", "SpoolingDiskWritingDuration(ms)", "SpoolingDiskReadingDuration(ms)", "SpoolingTotalDataSize(byte)","DataProcessingEndTimeUTC", "DataProcessingDuration(ms)", "Success", "ErrorMessage"}, {{"Count", each Table.RowCount(_), type number}, {"DataSourceType", each Text.Combine([DataSource.kind],","), type text}, {"DataSource", each Text.Combine([DataSource.path],","), type text}})

There is no gear icon with this step, so I have to manually edit it to fix the error (DataFormat.Error: There were more columns in the result than expected.     Details:     Count=17). I don't understand this step well enough to know what to change, though. Can I get some help with that part now?

P.S. One of the strange (to me) things is that the query is exactly the same here as it is for one of the servers where the reporting works. Shouldn't the fields, and so all the transformations like this row grouping, be the same on every server? 

[Anonymous]

Hi, @Belgarion42 

Before your Group step, you can use the Table.seletcolumns() method to select the columns, for example:

Step = Table.seletcolumns(#"Expanded DataSource1 - path", {"Index", "GatewayObjectId", "Source.Name", "RequestId", "QueryTrackingId", "QueryExecutionEndTimeUTC", "QueryExecutionDuration(ms)", "QueryType", "DataReadingAndSerializationDuration(ms)", "SpoolingDiskWritingDuration(ms)", "SpoolingDiskReadingDuration(ms)", "SpoolingTotalDataSize(byte)","DataProcessingEndTimeUTC", "DataProcessingDuration(ms)", "Success", "ErrorMessage"})

 Step after using your current Table.Group step.

Best Regards

Jianpeng Li

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

[Anonymous]

Thank you @amitchandak 

Hi, @Belgarion42 

There are many reasons for this error, you can follow the steps below to check your data source, Power Query query is correct:
I think you check that the table structure of your data source has not been changed. Then check each of your steps in Power Query and try to exclude the wrong columns from your steps. I found the following thread with the same problem:

Data Format Error: More columns than expected - Microsoft Fabric Community

DataFlow Refresh Error and Fix for added Fields in... - Microsoft Fabric Community

Also, you can check the thread below:

DataFormat.Error: There were more columns | Power Query | Excel Forum | My Online Training Hub

Best Regards

Jianpeng Li

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

[amitchandak]

@Belgarion42 , I think you need to open the file -> Go to power query refresh, and check. Usually, Power BI add additional columns and should not give errors. Check which query is giving issues on refresh and try to fix that