Reply
Frequent Visitor
Posts: 11
Registered: ‎07-29-2015

On-Prem SQL Query Refresh with Column Name change

HI all,

 

I built a dashboard based on a couple SQL queries from an on-prem DB in PBI Desktop. I uploaded the cloud Power BI site and have my gateway active along with my SQL credentials. All is well, however, when I try to fresh I get an erorr because the rowset "Sales Amount" cannot be found. This is a column I renamed in the Power Query portion of PBI Desktop. The original name is LineAmountMST. However, I thought the M language script stores this rename step and is able to make the connection to the column when refreshing. Does this mean we effectively cannot rename any columns and expect refresh to work?

 

Mark

Power BI Team
Posts: 75
Registered: ‎06-18-2015

Re: On-Prem SQL Query Refresh with Column Name change

If you try to refresh in Power BI Desktop does it work?  If it works in Desktop it should refresh in the service as well.

Frequent Visitor
Posts: 11
Registered: ‎07-29-2015

Re: On-Prem SQL Query Refresh with Column Name change

Yes it refreshes no problem in the PBI Desktop application. From the cloud, it's having trouble seeing the column name change. The gateway and everything is set up. Seems a bit odd. I did, however type a SQL query when I selected the "Get Data".

 

I read somewhere that refresh is not supported when you actually type a specific query. That also strikes me as odd. Why would we be required to select all from many tables and then manually join them and remove unneeded columns?

 

 

Member
Posts: 155
Registered: ‎07-24-2015

Re: On-Prem SQL Query Refresh with Column Name change

Strange enough. I have made several demo PowerBI reports using SQL queries to get data. I have renamed some of the columnns in the PBI desktop prior to publish the report to the service. The data refresh works fine both in the Desktop application and in the service as well.

 

 

Frequent Visitor
Posts: 11
Registered: ‎07-29-2015

Re: On-Prem SQL Query Refresh with Column Name change

I guess you are lucky. I've also encountered this problem if I rename the query from query1 to Customers or something. Are you doing anything differently? Can you give me an overview of your steps. Seems like it should be straightforward, but maybe I missing something.

 

refresh error.PNG

Member
Posts: 155
Registered: ‎07-24-2015

Re: On-Prem SQL Query Refresh with Column Name change

[ Edited ]

Sure,

 

This is one of the queries as shown in the Advanced Editor.

 

let
    Source = Sql.Database("srv------", "db----", [Query="select employeenumber#(lf), dailytimesheetline.locationname locationname#(lf), dailytimesheetline.jobnumber jobnumber#(lf), dailytimesheetline.taskname taskname#(lf), tasklistline.description taskdescription #(lf), numberof#(lf), convert (datetime, theDate, 102) entrydate#(lf), jobheader.companynumber companynumber#(lf), dailytimesheetline.entityname entityname #(lf)from dailytimesheetline#(lf)left outer join jobheader on jobheader.jobnumber= dailytimesheetline.jobnumber#(lf)left outer join tasklistline on tasklistline.taskname = dailytimesheetline.taskname and tasklistline.tasklist=jobheader.tasklist#(lf)where thedate>'2014.01.01'"]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"taskdescription", "Task Description"}, {"numberof", "Number Reg"}, {"taskname", "Task"}})
in
    #"Renamed Columns"

 

3 fields (columns) are renamed -  a measure (Number Reg)  an 2 non measure fields. The dataset with this query refreshes fine both in the PBI desktop and in the service (both scheduled and on demand)

Frequent Visitor
Posts: 11
Registered: ‎07-29-2015

Re: On-Prem SQL Query Refresh with Column Name change

When you pulled from SQL did you just use the table browser from power query? I tried this and refresh worked. I had to select an entire table in the list, and then I was able to change column names, delete columns, etc. However, if I explictly write a select statement and pull a join of tables into the model, the refresh will fail. 

Regular Visitor
Posts: 38
Registered: ‎07-10-2015

Re: On-Prem SQL Query Refresh with Column Name change

I have had this problem as well. I had to go back to the powerbi designer and undo the name change of column and republish in order to get the auto refresh to work correctly.
Member
Posts: 155
Registered: ‎07-24-2015

Re: On-Prem SQL Query Refresh with Column Name change

[ Edited ]

@minorThing, what do you call "PowerQuery" in the PowerBI desktop?

 

In the desktop, just go to Get Data, then type server, database and the sql statement and get the data, then if and when needed- edit query.

 

Can you give the steps to reproduce the problem? Or post the transformations of your query as seen in the advanced editor?

 

Frequent Visitor
Posts: 11
Registered: ‎07-29-2015

Re: On-Prem SQL Query Refresh with Column Name change

I open Power BI Desktop and click Get data. I type in a SQL query such as: select field1, field2, field3 from SalesTable. It pulls the data in, then I go into the query editor and rename the columns to Sales Amount, Sales Qty, Item Name. Then, I rename Query1 to "Sales". I did a similar process with another table, renaming columns and the query name, and then joined the two tables.

 

I designed some visualizations and saved it as a .pbix file. Then, I go to the website and upload the file. I see my visualizations and report. I go to the dataset and click refresh, and I get an error, The details say: Resource name and location need to match. Query1 Location: Sales. It's having trouble handling the rename when I use a written out SQL query to pull the data in, and I don't understand why.

 

I was able to get the refresh to work in a differnt scenario when I did not write a sql query, but rather chose a table from the UI browser (just typing in the sql server and database name brings up the list of tables). I chose the entire table and brought it into the query editor, did some renaming and editing, and followed the same steps to upload, and the refresh worked.