Help understanding Power BI error messages

[PhilB]

Hi,

So i have a dataset which is working, i can connect to it from the power BI desktop on my work machine and i have set up a scheduled refresh which is working also. 

The data set refreshes when i try in the online service, but i get an error message when using the desktop app. Unfortunately though, the error message i get is not very much help. And the data itself does refresh in the desktop.

Its the registrations table that is giving me the error.  After clicking to view the errors i get the following.

from what i can see, my visuals work, the auto refresh works. the power BI gateway is online and working. the dataset refreshes but still gives me the errors. The error message is pretty undescript, so could anyone explain it to me? or suggest where i would look? it is starting to become a bit irritating.

Thanks

Phil

[Squirtizzle]

Hello, 

Does anyone know what this error message is about? I have been running the same report with aking changes to the table for a few months now without any issues. I returned from my weekend and was not able to do a refresh on my web verion of power bi but I could do it on the desktop version. 

[Greg_Deckler]

Any chance you can post the steps of your query? Just thinking that perhaps a particular step like a type conversion might be throwing an error but not truly affecting the load? From your view errors, it looks like there really are no errors being generated which begs the question as to why it thinks that there are.

[Sean]

This reminds me of PowerQuery Errors in Excel

Look here => http://community.powerbi.com/t5/Desktop/Query-Errors-report/m-p/6673#M913

EDIT: Yes it seems they have implemented this feature now - that's great 

@konstantinosdon't know if you remember but you were asking about this (see above link)

EDIT2: Check the source for Registrations - has the table been renamed? or is it really empty?

In both cases I get that message The Table is Empty - otherwise it takes you to the errors!

[PhilB]

Hi @Sean and @Greg_Deckler, thanks for your replies.

The steps in my query are in the screenshot. Unfortunately they are not labled in any useful terms. This was a learning project that ended up being used.

I've also attached the query from the query editor if that helps. 

let
    Source = Table.NestedJoin(#"bitnami_wordpress wp_esp_registration",{"EVT_ID"},#"bitnami_wordpress wp_posts",{"ID"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"ID", "post_title", "post_type"}, {"NewColumn.ID", "NewColumn.post_title", "NewColumn.post_type"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded NewColumn",{"ATT_ID"},#"bitnami_wordpress wp_esp_attendee_meta",{"ATT_ID"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"ATT_ID", "ATT_fname", "ATT_lname", "ATT_city", "ATT_email"}, {"NewColumn.ATT_ID", "NewColumn.ATT_fname", "NewColumn.ATT_lname", "NewColumn.ATT_city", "NewColumn.ATT_email"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded NewColumn1",{"EVT_ID"},#"bitnami_wordpress wp_esp_people_to_post",{"OBJ_ID"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"PER_ID"}, {"NewColumn.PER_ID"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded NewColumn2",{"EVT_ID"},#"bitnami_wordpress wp_esp_event_venue",{"EVT_ID"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn3" = Table.ExpandTableColumn(#"Merged Queries2", "NewColumn", {"VNU_ID"}, {"NewColumn.VNU_ID"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded NewColumn3"),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"REG_session", "REG_url_link", "REG_att_is_going", "REG_deleted"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewColumn.ID", "Event ID"}, {"NewColumn.post_title", "Event Title"}, {"NewColumn.post_type", "Event.post_type"}, {"NewColumn.ATT_ID", "Attendee.ATT_ID"}, {"NewColumn.ATT_fname", "First Name"}, {"NewColumn.ATT_lname", "Last Name"}, {"NewColumn.ATT_city", "Attendee City"}, {"NewColumn.ATT_email", "Attendee Email"}, {"NewColumn.PER_ID", "Organiser"}, {"NewColumn.VNU_ID", "Venue ID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"REG_date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Event ID] <> null) and ([Attendee.ATT_ID] <> null) and ([STS_ID] = "RAP")),
    #"Merged Queries3" = Table.NestedJoin(#"Filtered Rows",{"EVT_ID"},#"bitnami_wordpress wp_term_relationships",{"object_id"},"NewColumn",JoinKind.FullOuter),
    #"Expanded NewColumn4" = Table.ExpandTableColumn(#"Merged Queries3", "NewColumn", {"term_taxonomy_id"}, {"term_taxonomy_id"}),
    #"Merged Queries4" = Table.NestedJoin(#"Expanded NewColumn4",{"term_taxonomy_id"},#"bitnami_wordpress wp_terms",{"term_id"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn5" = Table.ExpandTableColumn(#"Merged Queries4", "NewColumn", {"name"}, {"name"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded NewColumn5", each ([REG_ID] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"REG_ID", Order.Ascending}}),
    #"Removed Duplicates1" = Table.Distinct(#"Sorted Rows", {"REG_ID"}),
    #"Merged Queries5" = Table.NestedJoin(#"Removed Duplicates1",{"TKT_ID"},#"bitnami_wordpress wp_esp_datetime_ticket",{"TKT_ID"},"NewColumn",JoinKind.LeftOuter),
    #"Removed Duplicates2" = Table.Distinct(#"Merged Queries5", {"REG_ID"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Duplicates2",{"NewColumn", "term_taxonomy_id", "name"})
in
    #"Removed Columns1"

I hope it formats properly, apologies if not. 

The registrations table itself is a merged query, linking the wordpress posts and event information from the events plugin. We've merged them as finding all the relationships individually was proving difficult. If we were to do this again we would probably take the time to find the relationships and link it together properly. However the queries were working after we had done this.

We pull through the WordPress posts table, an attendee meta table, and events table, events meta, venue, and venue meta. They all link back eventually to pull the correct data for each registration.

I'm inlclined to think you are on the right track @Greg_Deckler as if there is an error, why no errors in the error report? and if it isn't an error then why does it say it is :S

@Sean I have checked the source, the tables are the same as they were from day one and if i look in the table, the rows are there with the information. Is that what you meant?

The 'table is empty' message that i get is shown when i click the error logs themselves, if i go back to my registrations table the data is there.

But as i say though, the data is updating when i run the refresh. I have checked in the events system on the website itself, and also check the mySQL DB in sql Workbench. 

It's been bugging me for a little while now, but it has been managable. but i've got some time to sit and try and fix it now. 

I have attached a screenshot of all the tables i have in power BI desktop app. The ones with the blue underlines are merged tables which i can provide queries if needed. The ones labeled GA are all pulled from Google Analytics. The rest are WordPress tables, which i havent altered.

Thanks for your time guys, appreciate any help you can provide. 

Regards,

Phil

[Anonymous]

Hi Phil,

I had similar issue before and it came from the column data type (which I hard coded)  not matching with its record data type.  

For example = Table.AddColumn(YearNumber , "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date])),type number), obviously it should type text.

Also, I found that it does not like Int64.Type. 

Nor sure if it applies to your case. 

Regards,

Anna

[kyle705]

Thanks Anna... Hard coded Int64.Type. seemed to be the problem for me!!!