Timed Refresh and pivoted columns

[Anonymous]

Hello All,

I'm going to be doing some "trial and error" but only have a short window to test this in and I need your advice. I have a report that I'm trying to publish to the service, the organization has a gateway to a local SQL server, however, to protect system performance its only available between midnight and 7am. The problem I'm running into, when I build the report against a replication server (non-production) then everything works perfectly, however, when I point it at the production server and refresh it, because the dataset is empty it comes back with an error saying that one of my pivoted columns doesn't exist.

Does the PowerQuery (M) action of pivoting a column only take place once? then, when it goes into the service does not refresh this? 

[Anonymous]

Hello, @collinq , I have probably the same issue with pivot step in power query, on desktop works but on refresh from PowerBI service gives the error on missing column, one of those that are pivoted. I have set parameters RangeStart and RangeEnd for the incremental refresh policy that are uaed as a last step in power query after pivot step. I don't understand how the Anonymous user on this topic solved the issue. Should I change the parameters RangeStart and Ranve End to point to the same period for which I load the data in PowerBI desktop in order to perform a refresh with PowerBI service?

[Anonymous]

Hi @Anonymous ,

It seems the pivoted column doesn't exist when point to production server. Whether the table structures in production server are same with the ones in replication server (non-production)? The following documentations are about pivot columns and refresh, hope they can help you.

Data refresh in Power BI

Pivot columns

Pivoting Data In Power Query

Best Regards

Rena

[Anonymous]

Hello All,

I'm working with the IT dept. to figure this out. What I need to know is if the PowerQuery (M) portions need to take place in the PBI desktop or if I can upload it "as is" with a null dataset that will refresh properly in the service.

Thank you,

[Anonymous · Accepted solution]

Hello All,

This may be "news" to some, this may not be news to others. However, I have solved my issue and learned more about the power BI service in the process. Please read below for the methodical approach to the fix:

Method to fix:

Worked with internal IT dept. to remove the security restriction temporarily. With restriction removed, refreshed the dataset and everything worked 100%. Confirmed scheduled refresh will work by refreshing the data in the model in the service.

Likely cause:

Steps applied in power query need to take place in the desktop with the data present. When the data set is null the data-dependent steps do not take place in the service and only in the desktop.

Best Practices:

Ensure that the report works perfectly in desktop before uploading to BI Service. Ensure that data-dependent power query steps have data when refreshing to ensure that all steps can be applied.

[collinq]

Hi @Anonymous ,

If your dataset is empty then the error is correct.  If you are trying to do a step in M based on the dataset, then the data has to be there for you to be able to manipulate it.  If there is no data then most steps won't work (like Replace, Sort, Filter, etc).  Those steps require more than column headers to work.  Your pivot column is the same process most likely.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[Anonymous]

Hello,

That was my assumption. I was just hoping I hadn't bumped into a "weird limitation" of the BI Service that prevented me from using pivoted columns in a dataset. I'll just have to publish and refresh during that window of time the first time and then it should work from there on out. My assumption was that it would pick it up on its first refresh, however, chances are there is a step that gets done at the time of publish that needs the data to be present for.

Thank you,