Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
@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
@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
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |