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
ninsights
Helper III
Helper III

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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
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.