Solving an error in my report query

[Deborah1982]

HI,

If you normally have an error when you try to update, or refresh a query, the system shows you what the error is.

For examples duplicates, of errors in the file you have imported.

However this time I have an error, and when you click on the 'show error', the table I get is empty.

I will attach views:

1) view of the original error

2)after I click view view error, this is the screen I get

All the data that is imported in the other tables worked, this table is a combined table of all above years together.

ex: = Table.Combine({#"2013", #"2014", #"2015", #"2016", #"2017"})

There was no error first, than we updated some data in the sources, all was refreshed without errors exept this error in the combined table called 2013-2017

Who can help me solve the error?  I have no idea where BI is referring to?

PS: refresh is working, Close and Apply -- then the error is appearing.

This is the error messageTo view error, nothing is shown

Kind regards

Deborah

[MarcelBeug]

This situation typically happens when you have data that doesn't meet the column type of your table in Power Query.

An example would be: decimal numbers in a column with type Int64.Type (Whole Numbers).

Power Query allows this (so there is no error in your query editor, nor in your error report), but when loading the data, errors are reported.

It may require some searching to locate the issue.

[Deborah1982]

HI,

I have checked the data type, and indeed 2 columns were different from the other tables, but after fixing, still the same errors.

I think that all my columns have the correct data type.

How can I be sure?

Kind regards

Deborah

[MarcelBeug]

Edit: I prepared this post before I noticed the previous post.

I have something to check the contents of a table, but this sould be regarded as experimental, for use at your own risk and definitely not directly with your production file.

You can find some queries in this pbix file to check the contents of a table.

The pbix file contains an example table Table1.

If you invoke function fnCheckTable for Table1, then you get a copy of the table with any values replaced by errors, if the values do not conform to the column type. In Table1 there is some text in a numeric clumn and vice versa, so these come back as errors.

You can download the pbix file, copy your table into that pbix file and run the check for your table.

I don't know how long the check will run for large tables (to be honest I think it may run quite long).

Again: this is at your own risk, but maybe it will be helpful.

I'm really curious about your findings.

[MarcelBeug]

The Table.Combine looks fine to me.

My suggestion would be to try and combine varous combinations of tables in order to find out which table is causing the issue, and proceed from there.

[Deborah1982 · Accepted solution]

HI,

OMG you were right.  There was a column in xls that should have been 123 values, and no decimals, but they did. So upload was working, but this was giving the error.  Once my xls was corrected, the error was gone.

Thank you very much.

Kind regards

Deborah

[Deborah1982]

Hi,

Can the reason be how i created the column of all years.

So I have columns 2013, 2014 2015 2016 & 2017 uploaded out of xls files.  I need one big file to work with.

I have done this by filling in:

= Table.Combine({#"2013", #"2014", #"2015", #"2016", #"2017"})

Is this the correct way?

Maybe this is why I have the errors?  I cannot find a difference anymore with data type.

Kind regards

Deborah