Forum Discussion

ninsights's avatar
ninsights
Helper III
4 years ago
Solved

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.

 

how to
write to SQL
  • smpa01's avatar
    smpa01
    4 years ago

    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 = "[email protected]", 
  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

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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.

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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 = "[email protected]", 
  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

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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