Table that contains a pivot not updating on Power BI Service

[pbizombie]

Hello, 

I have a table in an SQL database with the following data: 

I Import this data into Power BI and create a pivot on it so that the values in the ColName Column become columns

I then save this pbix and upload it to my Power BI Service.  All is working well so far and the columns are visible.

The problem is when I add a new entry to the table that should pivot to a new column, it doesn't work and the new column is not created: 

Data is added to the SQL table and then dataset is refreshed in the Power BI service, however Col4 does not appear in the pivoted table

However if I go back into the Power BI Desktop and hit refresh the changes appear in the desktop version.

Does anyone know how I can get these changes to also happen in the Power BI Service when the dataset refreshes? 

Reuploading the pbix file every time isn't an option as we have hundreds of clients with different databases so it needs to happen automatically on refresh of the dataset.  

Thanks in advance. 

[luisrh]

I think the train has left the station on this issue.  The way it's handled in PBI desktop engine is different and there is no way create the pivot this way.    I would suggest looking at a separate approach which is what I did.

regards

[Anonymous]

Have the same issue with pivot done in power query and error on refresh in PowerBI service. I have moved the pivoting step outside, using DAX but it takes too much memory and receive error on memory limig. Is this solved ?

[luisrh]

Yes.. it looks like there are many moving parts to this and they will not change it.   

[rdumont]

i've submitted to support, will advise what they say

[rdumont]

Support says:

To put it simply, Power BI Desktop refresh vs Power BI service refresh is different. Power BI Desktop will refresh 3 things, visuals, data, and schema. In Power BI service, during a dataset refresh, only data refresh happens. The data within the dataset will get updated but the model (transformations) remains the same at the time of publishing.

[jamezhang]

Documentation for reference: https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data#power-bi-refresh-types

[rdumont]

Still an issue in 2023, is this "intended" functionality?

[Anonymous]

Hi @pbizombie ,

Have your problem be solved? 

Best Regards,

Jay

[pbizombie]

Hi @Anonymous 

Yes the dataset is definitely refreshing according to the refresh history, if I view the original normalized table in the service the new data appears, but the table with the pivot step on it does not create the new column. 

Thanks 

[luisrh]

Was there a resolution to this?

I am having the same exact problem. 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 file will have the same result.

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.

[Anonymous]

Hi @pbizombie ,

Check the refresh histoy make sure the dataset has been refreshed successfully in Power BI Service.

Click the refresh button and check if the underlying data has been changed.

Best Regards,

Jay

[Greg_Deckler]

Would need to see the Power Query code from Advanced Editor.

[pbizombie]

Hi Greg, 

Here it is: 

let
Source = Sql.Database("testServer", "DEMODB"),
dbo_ZDemoTable = Source{[Schema="dbo",Item="ZDemoTable"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_ZDemoTable,{"ID"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[ColName]), "ColName", "Value")
in
#"Pivoted Column"

Thanks

[Anonymous]

Is this problem solved after 2 years? I also had this problem today. One of the values in the to-be-pivot column was removed, let's say value "C". So the pivoted columns shouldn't have column "C" anymore. But the dataset in the PB sever still show column "C" and "C" still exist in that to-be-pivot column. And when I refresh, there is a error message saying "column "C" doesn't exist in the rowsets of that table". Which is odd. Because this message should only appear if I don't have column "C" and some later procedure used column "C". But none of my later procedure use column "C".

[Pavithran1]

Did you find any solution on this issue. I have same issue where my dataset refresh is failing as the row is deleted and my pivot table is expecting that column