cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ninsights
Helper II
Helper II

Writing Calculated Visualization Back to SQL Table

My Power BI file is published to the Power BI service. The data source is a SQL Server.

I have the following visualization in my file where QtyOnHand is a CALCULATED field. I would like to write this data visualization back to a table in the SQL server. Is this possible or can you only write back fields that you can see in the Transform data section. Since this is a calculated field, it does not appear in the Transform Data section of Power BI.

ninsights_0-1639778459796.png

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@ninsights  if you have published this on a SSAS server (pbi workspace for example), you can write a PQ where you query the same SSAS server and utilize the same DAX query that generates  this viz  and try to write back the results in a SQL table. 

 

An example of SQL write back with PQ

 

let
  Source   = Sql.Database("myserver", "newtest"), 
  emailVal = "2@xyz.com", 
  Custom1  = "INSERT INTO [newtest].[dbo].[Table_1] (Email) VALUES('" & emailVal & "')", 
  Custom2  = Value.NativeQuery(Source, Custom1)
in
  Custom2

 Something like this

 

let
Source = Sql.Database("myserver", "newtest"),
SSAS query = SSAS Q with DAX query,
Val = convert the SSAS query to SQL insert row,
Custom1 = "INSERT INTO [newtest].[dbo].[Table_1] (Email) VALUES('" & Val & "')", Custom2 = Value.NativeQuery(Source, Custom1)
in Custom2





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@ninsights  if you have published this on a SSAS server (pbi workspace for example), you can write a PQ where you query the same SSAS server and utilize the same DAX query that generates  this viz  and try to write back the results in a SQL table. 

 

An example of SQL write back with PQ

 

let
  Source   = Sql.Database("myserver", "newtest"), 
  emailVal = "2@xyz.com", 
  Custom1  = "INSERT INTO [newtest].[dbo].[Table_1] (Email) VALUES('" & emailVal & "')", 
  Custom2  = Value.NativeQuery(Source, Custom1)
in
  Custom2

 Something like this

 

let
Source = Sql.Database("myserver", "newtest"),
SSAS query = SSAS Q with DAX query,
Val = convert the SSAS query to SQL insert row,
Custom1 = "INSERT INTO [newtest].[dbo].[Table_1] (Email) VALUES('" & Val & "')", Custom2 = Value.NativeQuery(Source, Custom1)
in Custom2





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


v-henryk-mstf
Community Support
Community Support

Hi @ninsights ,

 

Agree with @AlexisOlson  statement. Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Writing the visual part of the code back to sql does not seem to be a routine operation.


Best Regards,
Henry

 

AlexisOlson
Super User
Super User

Generally speaking, Power BI is only intended to read data, not to write data back to a source. There are always methods of exporting and importing data from one place to another but I wouldn't recommend attempting to do this if you can avoid it.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors