Forum Discussion
Converting ColumnsUpdated Field
In my SQL datatable I have a ColumnsUpdated field which is a VARBINARY representation of the COLUMNS_UPDATED T-SQL function. I have a custom scalar-valued function which converts the column by essentially using the sys.fn_IsBitSetInBitmask function.
In SQL to get the names of the columns that were updated I create a new query, declare a VARCHAR variable, set the declared variable's value to the result of executing the function, and then print the variable's new value.
In Power BI I don't see anything similar to this, am I overlooking something?
Greg_DecklerI was able to solve this issue by created a CTE in the Advanced Options of Get Data, setting up the relationship, and then displaying the parsed ColumnsUpdated from the CTE in a report.
The SQL I ran during import was:
WITH ChangeLog_CTE (Guid, ParsedUpdatedColumns) AS ( SELECT Guid, (dbo.[GetChangedColumns]('History.Resources', History.Resources.ColumnsUpdated)) AS ParsedUpdatedColumns FROM History.Resources ) SELECT * FROM ChangeLog_CTE;
4 Replies
I'm following about half of this. In Power BI you don't generally update existing columns and tables, it is for reporting.
My apologies, I should have been clearer in my original post.
The table that I am referring to is a temporal table and in this table there is a column named ColumnsUpdated. The ColumnsUpdated column is a VARBINARY column that is the result of the COLUMNS_UPDATED function via a trigger. What I'm attempting to do is created a Power BI report in which it displays a delimited list of columns that were updated for a given row (requirement A) as well as the user's name of who updated the row (requirement b).
Currently in my database I have a scalar-valued function that returns a delimited list, this would solve requirement A. I understand how to run the function in SQL to return the delimited list, but I don't understand how to run the function in Power BI.
Greg_DecklerI was able to solve this issue by created a CTE in the Advanced Options of Get Data, setting up the relationship, and then displaying the parsed ColumnsUpdated from the CTE in a report.
The SQL I ran during import was:
WITH ChangeLog_CTE (Guid, ParsedUpdatedColumns) AS ( SELECT Guid, (dbo.[GetChangedColumns]('History.Resources', History.Resources.ColumnsUpdated)) AS ParsedUpdatedColumns FROM History.Resources ) SELECT * FROM ChangeLog_CTE;dday9 , glad you got it figured out. Yes, the Advanced Options will let you run whatever SQL Query you desire. Didn't think of that as the answer to your question because I'm still not exactly sure what you are going for!! :)
