Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luisrh
Responsive Resident
Responsive Resident

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.

7 REPLIES 7
yaniv-a
New Member

Same problem here. Any ideas?

lbendlin
Super User
Super User

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?

luisrh
Responsive Resident
Responsive Resident

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.

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.

luisrh
Responsive Resident
Responsive Resident

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.  

 

 

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors