cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver III
Resolver III

Table.Pivot not working on Power BI service. Works on PBI Desktop.

 

I am having a problem that is similar to someone who posted that never got a response. the Table.Pivot never adds the columns to the table.   Power BI desktop works perfectly,  when file is uploaded and a refresh runs in the service it finishes succesfully.   When I go look at the tables,  though,  the pivoted columns do not show up..  

 

I have tried this with PBI desktop June 2020 and July 2020.  Both files when uploaded to the service will have the same result when a refresh happens.

 

The Table.pivot code for one of the tables is below...

 

let
Source = CustomFields,
#"Filtered Rows" = Table.SelectRows(Source, each ([CustomFieldType] = "date_input")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"CustomFieldType"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[CustomFieldLabel]), "CustomFieldLabel", "CustomFieldValue", List.First)
in
#"Pivoted Column"

 

Any help would be appreciated.

6 REPLIES 6
Highlighted
Community Champion
Community Champion

Re: Table.Pivot not working on Power BI service. Works on PBI Desktop.

Are you sure it's the Table.Pivot part that creates the issue? What if you don't pivot (ie leave the data as is before that step) , then show the pre-pivot data in a visual, publish to workspace, and refresh the dataset there?

Highlighted
Resolver III
Resolver III

Re: Table.Pivot not working on Power BI service. Works on PBI Desktop.

I already have the pre-pivoted data in a visual (Crosstab).    The need to have the columnar data to more easily create visuals tied to other dimensions is the reason we had to do the pivot.   the odd thing is that it worked initially on the first refresh,  afterwards it did not work.  Nothing was changed.   I deleted and republished by hand -  the refresh finishes and the pivoted columns are gone.

Are there any diagnostics on the Embedded Service that would be helpful in seeing where the issue may be?   It looks like a bug.

thanks.

Highlighted
Community Champion
Community Champion

Re: Table.Pivot not working on Power BI service. Works on PBI Desktop.

The only thing that comes to mind is that the mashup engines may be out of sync between desktop, service, and gateway (in case you use a gateway).  You can't really control the engine on the service but you can influence the one on the gateway (and you can enable advanced logging to see if it renders what it promised or if it quietly dropped a few electrons.

Highlighted
Resolver III
Resolver III

Re: Table.Pivot not working on Power BI service. Works on PBI Desktop.

Thank you..   The data is coming from our OData endpoint.  I am going to have to open a case with MSFT.   It is really odd, because if I put a fully refreshed model from Power BI Desktop on the service,  and  refresh it, it does what it's expected.   If I put an empty model up there,  it will not work.  I wonder if it requires the metadata for the columns on the service.  

 

 

Highlighted
Community Support
Community Support

Re: Table.Pivot not working on Power BI service. Works on PBI Desktop.

Hi @luisrh ,

 

If you have resolved this issue, please share your solution here. Other users who encounter the same situation will benefit from this thread.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Resolver III
Resolver III

Re: Table.Pivot not working on Power BI service. Works on PBI Desktop.

No.. I haven't resolved this issue.  I believe it's a bug on the service and hoping someone from MSFT will look at it and try themselves.  it's pretty simple to reproduce.    Let me know if you want more details.

thanks

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors