Self entered data table cleared after refresh from Service-tool

[CJMolin]

Hello,

First of all Im not sure if this topic should lay under Desktop or Service since it concerns them both, sorry if I put it wrong.

Background:

I've created a pretty big data model in PowerBI desktop which runs perfectly fine by a scheduled refresh setup in the Power BI Service. One of the tables are "self entered" using the "enter data" function. I use this table as a lookup table creating measures calculacting performance from this "hardcoded" table.

Problem:

When I am refreshing my dataset in Power BI Desktop everything is working as it is supposed to. ALTHOUGH! When I publish my report to the service interface and i refresh the report from there my "self entered" data table clears and my measures related to this table of course will be blank in the service interface.

I have no idea how to solve this and Ive been googling alot! Anyone know how to fix this issue? 

Thanks alot!!!

CJ

[cs_skit]

In PBI Desktop if you go to Query Editor and remove that checkbox:

for the table in question only (right click it) before publishing

does that fix your issue?

[CJMolin]

Hello,

Actually i found that option and tried it without any success. It still cleared my table! Thank you for quick reply @cs_skit

[cs_skit]

Yeah sorry for those static tables I usually icreate the table in Excel quickly import and then stop the refresh I actually never published Enter Data stuff so far let me try that fast 🙂

[CJMolin]

So you mean I should use an Excel file as a datasource containing the information I just put in using "Enter data"? @cs_skit

[cs_skit]

Yeah you can do it its quick to create and import and if its just a static table disable refresha nd its fine

You could also type the table manually in M but your probably more comfortable with Excel.

In PowerQuery/Query Editor when importing from Excel use "First Row as Header" function after you imported.

Strangely I just tried with "Enter Data" and I did not get the problem you are having data stays on refresh.

[CJMolin]

Hi,

Even after you refreshed it from the Service interface? I have no idea what could be wrong.

Now I am facing "credential problems" when im trying to use an excel file as a data source.... im getting crazy over here 🙂

[cs_skit]

WTF??? that sounds strange... these are the steps

• You are in PowerBI Desktop
• Click Get Data - Excel
• Load Excel file from your PC into PBIX as new table
• Disable Refresh on that table in Query Editor
• publish to Service

can you maybe post some screenshots if you still have problems?

[CJMolin]

Its so strange.. My current report which is EXACTLY the same, but with entered data instead of Excel data source data..

heres the message i get so i dont even know if the data clears or not.... 😞

[v-qiuyu-msft]

Hi @CJMolin,

From the screnshoot, the dataset contains on-premise data source, so it requires gateway to act bridge between the on-premise data gateway and on-premise data source.

In your scenario, please download the on-premise data gateway and install it follow this article: On-premises data gateway . Then you can go to Manage Gateways, create two data source use the same connection information as used in Power BI desktop. Take the SQL Server data source as example, you can see: Manage your data source - SQL Server.

Or you can install the On-premises data gateway (personal mode), then you can see on-premise data gateway (personal mode) is available and you are able to edit data source.

Best Regards,
Qiuyun Yu

[CJMolin]

Something that might work is if there is a possibilty to "disable" my static self entered data table to be refreshed when im trying to refresh through the PBI service.. is that possible @v-qiuyu-msft?

[v-qiuyu-msft]

Hi @CJMolin,

In Power BI service, we are not able to refresh a part of data source in one dataset.

I'm not reproduce the issue on my side. In your scenario, please open the downloaded .pbix file in Power BI desktop version 2.48.4792.721 64-bit (July 2017).

Best Regards,
Qiuyun Yu

[CJMolin · Accepted solution]

Thank you for all your help Qiuyu.

I was able to fix this issue with the help of one of our IT guys at work. He put up a new database for me so I can use it for lookups (so i had a source to point at at refresh).

[CJMolin]

Thank you very much for your long answer. I realised it could be working if I was using a personal gateway. Unfortunately we're not allowed to use personal gateways on the company since we want to be all cloud based. So that brings this thread back to the main question I supppose.. 😞

Thank you very much again!

CJ

[v-qiuyu-msft]

Hi @CJMolin,

For your original issue, I mean when you use entered data instead of connect to local Excel in the dataset, which kind of data source did you use in the dataset?

In Power BI service, as long as the dataste contains on-premise data source (eg: SQL Server database, local Excel file), we need to utilize the gateway to build bridge between the Power BI service and on-premise data source. Is there any specific reason that you can't use gateway?

If you don't want to use gateway, you can enter all data when you create the report in desktop. Or just connect to online data source which not require gateway, for example: Azure SQL database.

Best Regards,
Qiuyun Yu

[CJMolin]

Hello again!

I am using a Gateway, but not a personal gateway, see picture attached. The other connection is to a MySqlServer..

 My gatewaymy self entered data table in power bi desktop. data still there if i refresh in power bi desktopafter published my dataset to service i try to refresh. all of a sudden my meaures are blank due to the table = blanki then download the report again to see whats wrongafter downloaded my refreshed report in pb service i realize this table is now empty.